Adding DuckDB Macros

[taras]

Is your feature request related to a problem? Please describe.
DuckDB macros are temporary. They're scoped to a connection where they're created.

Describe the solution you'd like
I would like to be able to install macros by providing SQL that will be invoked when a connection to DuckDB is first created.

Describe alternatives you've considered
Not using macros would suck because there are some good once - such as duckdb/duckdb#3578 (comment)

Additional context
https://duckdb.org/docs/sql/statements/create_macro.html

[github-actions]

If you are interested in working on this issue, please go ahead and provide PR for that.
We'd be happy to review it and merge it.
If this is the first time you are contributing a Pull Request to Cube, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube Slack.

[taras]

@paveltiunov can you suggest a general shape for this solution? What would be the appropriate extension point for specifying the SQL that should be injected? Is there a reference point of how it's done in other places?

There seems to be a initSql configuration option, but I can't see how to set it.

[taras]

👋 @ovr I noticed that you introduced initSql property in #8955 but I can't see how it can be used. Any suggestions would be much appreciated.

[taras]

I figured that I can install macros by creating a custom DuckDB Driver by adding the following to cube.js

const DuckDBDriver = require('@cubejs-backend/duckdb-driver');

module.exports = {
  driverFactory: () => {
    return new DuckDBDriver({
      initSql: `
        CREATE OR REPLACE MACRO json_each(val) AS
        TABLE (
          SELECT CASE
            WHEN json_array_length(j) >0 THEN unnest(range(json_array_length(j)::BIGINT))::VARCHAR
            ELSE unnest(json_keys(j)) END as key,
          CASE
            WHEN json_array_length(j) >0 THEN
            json_extract(j, key::BIGINT) ELSE
            json_extract(j, key)
          END as value
          FROM (SELECT val as j)
        );
      `
    })
  }
};