Greetings all, I am updating the :create-table macro to work with Greenplum's CREATE TABLE syntax. For the most part, this is identical except for two important table attributes:
DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY
These tell Greenplum how to distribute the table across the cluster, and getting these right is critical for good performance. Here is a SQL example using the FAA dataset:
create table faa.d_airports ( AirportID integer, Name text, City text, Country text, airport_code text, ICOA_code text, Latitude float8, Longitude float8, Altitude float8, TimeZoneOffset float, DST_Flag text , TZ text ) distributed by (airport_code);
The challenge is in emitting the 'distributed by (...)' or 'distributed randomly' after the closing parenthesis in the SQL. The S-SQL macro, with the correct location is: (def-sql-op :create-table (name (&rest columns) &rest options) (when (null columns) (sql-error "No columns defined for table ~A." name)) `("CREATE TABLE " ,(to-sql-name name) " (" ,@(loop :for ((column-name . args) . rest) :on columns :append (expand-table-column column-name args) :if rest :collect ", ") ,@(loop :for ((option . args)) :on options :collect ", " :append (expand-table-constraint option args)) ")" ; DISTRIBUTED BY needs to go here )) Initially I thought I could modify expand-table-constraints in a way that would do the trick, but I could not find a way to do this without either hardcoding these two cases or modifying the function signature. Modifying the signature of the :create-table macro seems the best choice, and then add another invocation of expand-table-constraint after the closing parentheses, but that is a breaking change. Alternatively we could have a :create-gp-table, but I would rather not diverge. So, I am here to ask: am I missing something obvious? If not, what is the consensus for moving forward? I supposed I would be most in favor of modifying the signature for :create-table. Regards, Steve P.S. There is also an open issue for create table that might have a need for something similar. I do not know, but it is worth considering both of them whilst making the change.