Databricks loader: Support for generated columns

[istreeter]

For optimum table partitioning, we want to use a auto-generated column on the date of collector timestamp. I imagine a table definition something like this:

CREATE TABLE events(
  app_id    VARCHAR(255),
  --
  -- lots of other columns here!
  --
  collector_tstamp_date DATE ALWAYS GENERATED AS (DATE(collector_tstamp))
)
PARTITIONED BY (collector_tstamp_date, event_name)

I have found that with generated columns we occasionally get exceptions with messages like:

Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: [MISSING_COLUMN] org.apache.spark.sql.AnalysisException: Column 'unstruct_event_com_acme_myevent_1' does not exist.

I think it's something to do with how we use the MERGESCHEMA copy option, without explicitly setting the table schema, and because different batches can have different sets of entities. These seems to be inconsistent with generated columns.

The solution I've found is to always specify every single column in the table in the COPY INTO statement. If the column is not in the parquet file then select it as NULL AS unstruct_event_com_acme_myevent_1.