CALL `justfunctions.eu.generate_justsql_schema`("justfunctions","eu","['date_calendar']")
/*--Output--
{ "tables": [{ "table_name": "justfunctions.eu.date_calendar", "columns": [{ "name": "date","type":"DATE"},{ "name": "Quarter","type":"STRING"},{ "name": "month_name","type":"STRING"},{ "name": "epoch","type":"INT64"},{ "name": "date_id","type":"INT64"},{ "name": "day_of_month","type":"INT64"},{ "name": "day_suffix","type":"STRING"}] }] }
*/
CREATE OR REPLACE PROCEDURE `your_project_id.your_dataset_id.generate_justsql_schema`(`project_id` string, `dataset_id` string, `tables` array<string>)
options(
description = '''Generates a JSON schema for use with JustSQL GPT using the specified <project_id>, <dataset_id>, and list of <tables> (https://chat.openai.com/g/g-hzlGYume7-justsql-for-bigquery).'''
)
BEGIN
DECLARE query_string STRING;
DECLARE json_schema STRING DEFAULT '';
DECLARE tables_string STRING;
SET tables_string = ARRAY_TO_STRING(ARRAY(SELECT '\'' || x || '\'' FROM UNNEST(tables) AS x), ',');
SET query_string = CONCAT(
'SELECT ',
'CONCAT(\'{ "tables": [\', STRING_AGG(table_schema_, \',\' ORDER BY table_name), \'] }\' ) AS json_schema ',
'FROM ( SELECT table_name,table_schema,table_catalog,',
'CONCAT(\'{ "table_name": "\', CONCAT(table_catalog,\'.\',table_schema,\'.\',table_name), \'", "columns": [\', STRING_AGG(CONCAT(\'{ "name": "\',column_name,\'","type":"\',data_type,\'"}\'), \',\'),\'] }\') AS table_schema_ ',
'FROM \`',project_id,'.',dataset_id,'.INFORMATION_SCHEMA.COLUMNS\` ',
'WHERE table_name IN (', tables_string, ') ',
'GROUP BY 1,2,3 ) A'
);
EXECUTE IMMEDIATE query_string INTO json_schema;
SELECT json_schema;
END;
User Defined SQL Procedure
See something wrong? Contact us or report an issue on Github.