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.
