generate_justsql_schema - BigQuery

Function

generate_justsql_schema(project_id, dataset_id, tables)

Description

Generate Justsql Schema - 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).

Example Query

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"}] }] }
*/

Statement

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;

Regions

justfunctions.eu.generate_justsql_schema(project_id, dataset_id, tables),
justfunctions.us.generate_justsql_schema(project_id, dataset_id, tables)

Source

Type

User Defined SQL Procedure

How to Use

Frequently Asked Questions

User-Defined Functions (UDFs) in Google BigQuery are custom functions that you can create to perform operations that aren't available through the standard SQL functions. These UDFs allow you to extend BigQuery's SQL capabilities to suit your specific data processing needs. JustFunctions is a collection of open-source user-defined functions (UDFs).

JustFunctions is a collection of open-source User-Defined Functions (UDFs) designed to extend the capabilities of Google BigQuery. These functions cover a wide range of applications, including text manipulation, URL processing, date processing, email handling, similarity measures, and more. Moreover, JustFunctions is frequently updated to include more use cases.

We welcome any feedback or questions you may have. You can Contact us or report an issue on Github.

Functions and procedures from JustFunctions can be used directly in any of your projects.
To start, simply click 👆 on any function,  Copy  the 'Example Query' and run it in your BigQuery console.
You can also  Copy  the 'Statement' to create your own private user-defined function.

Yes, JustFunctions is completely free to use.

Yes, currently JustFunctions is only available for Google BigQuery. In the future, we will also support PostgreSQL.

See something wrong? Contact us or report an issue on Github.