Generating 50+ SQL Statements in Under 10 Minutes; No Hand Written SQL!
The AltaSQL SELECT Discover Demo uses the Chinook Music database whose columns are in CamelCase. This is the same demo and script used in the Snowflake Marketplace listing.
In this demo, we will generate and execute 50+ SQL statements, including:
using AltaSQL Ready to Execute Defined Functions. We simply copied and pasted the function calls from the Excel Spreadsheet, set the argument value and executed. Most of these were use to generate multiple SQL statements.
The only hand written statements are queries on the repository.
At the end, we will effectively provide end users with the ability to generate 2 forms of SELECT statements on all of the Chinook tables, using only the AltaSQL Defined Function
SELECT SBLD_SELECT(<view_definition_name>;
All generated SELECT statements will be in column alias syntax with:
Half of the queries will be in our SELECT * Replacement form with full database qualification. Sample columns and the generated FROM Clause:
领英推荐
-- <database.schema.table>.<column> AS <column>
SELECT
"CHINOOK_DB"."CHINOOK_PHYSICAL_SCHEMA"."Customer"."CustomerId" AS "CustomerId",
"CHINOOK_DB"."CHINOOK_PHYSICAL_SCHEMA"."Customer"."FirstName" AS "FirstName" ,
"CHINOOK_DB"."CHINOOK_PHYSICAL_SCHEMA"."Customer"."LastName" AS "LastName" , ...
FROM
"CHINOOK_DB"."CHINOOK_PHYSICAL_SCHEMA"."Customer";
The other half of the queries will have their AS <column> with CamelCase changed to natural language with spaces (<NL_column>), all properly quoted. The qualification for Customer will be the table alias "CUST_" We also added the Customer Account Number column. Sample columns and the generated FROM Clause:
SELECT
CUST_."CustomerId" AS "CustomerId" ,
CUST_."CustomerId" AS "Customer Account Number",
CUST_."FirstName" AS "First Name" ,
CUST_."LastName" AS "Last Name" , ...
FROM
"Customer" AS CUST_;
This set of queries will use the table name as the qualifier. We also added an an expression to calculate the commission to for Invoices to the View Definition:
SELECT
Invoice."InvoiceId" AS "InvoiceId" ,
Invoice."CustomerId" AS "CustomerId" ,
Invoice."InvoiceDate" AS "Invoice Date" ,
Invoice."BillingAddress" AS "Billing Address" ,
Invoice."BillingCity" AS "Billing City" ,
Invoice."BillingState" AS "Billing State" ,
Invoice."BillingCountry" AS "Billing Country" ,
Invoice."BillingPostalCode" AS "Billing Postal Code",
Invoice."Total" AS "Total" ,
CASE
WHEN "Total" > 20
THEN CAST ("Total" * 0.1 AS NUMBER(8,2))
WHEN "Total" > 13
THEN CAST ("Total" * 0.075 AS NUMBER(8,2))
WHEN "Total" > 5
THEN CAST ("Total" * 0.05 AS NUMBER(8,2))
ELSE CAST ("Total" * 0.02 AS NUMBER(8,2))
END AS "Commisson"
FROM
"Invoice" AS Invoice;
Finally, we will demonstrate how end users can generate SQL statements, but they must have appropriate privileges to execute the statements.
You can test this yourself with the script below. You don not need to create the Chinook Database. You can use this same basic script on your own data; it will handle any number of Snowflake objects.
Get our free AltaSQL SELECT Discover from the Snowflake Marketplace and try it yourself. Fill in the SET statements; note that if your shared database is not named ASQL_DB, globally replace ASQL_DB with the appropriate database name. The script does not include the setup for privilege testing.
--
SET SOURCE_DB = <shared_database_name>; -- typically ASQL_DB
SET SOURCE_SCHEMA = 'CHINOOK_PHYSICAL_SCHEMA';
SET REPO_DB = <your repository database>;
SET REPO_SCHEMA = <your repository schema>;
SET REPO_NAME = <your repository name>;
--
SELECT
ASQL_DB.ASDF_SCHEMA.SBLD_REPO_FROM_INFO_SCHEMA (
META_REPO_NAME => $REPO_NAME,
TABLE_CONTROL => 'OR_REPLACE',
TO_DB => $REPO_DB,
TO_SCHEMA => $REPO_SCHEMA,
SOURCE_DB => $SOURCE_DB,
SOURCE_SCHEMA => 'CHINOOK_PHYSICAL_SCHEMA' ,
VIEW_PREFIX => 'VW_',
VIEW_SUFFIX => '_BASE',
DATA_RETENTION_TIME_IN_DAYS => 'NULL', OBJECT_TAG => 'NULL', ROW_ACCESS => 'NULL', POST_WHERE_KEYWORD => 'NULL');
--
-- Set up the Repository Context
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_REPO_SESSION_VARIABLE(
REPO_DB => $REPO_DB,
REPO_SCHEMA => $REPO_SCHEMA,
REPO_OBJECT => $REPO_NAME);
--
SET SBLDR_META_REPOSITORY = <result of function call, copy into other worksheet>;
--
-- Show View Definitions
--
SELECT DISTINCT(VIEW_NAME) FROM IDENTIFIER($SBLDR_META_REPOSITORY) ;
--
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_SELECT(repo_view_name => 'VW_Invoice_BASE');
--
SELECT
ASQL_DB.ASDF_SCHEMA.SBLD_SELECT(repo_view_name => 'VW_Customer_BASE',FROM_CLAUSE => 'NULL',POST_FROM_CLAUSE => 'WHERE "CustomerId" = 15');
--
-- Add AltaSQL Tag
--
SELECT
ASQL_DB.ASDF_SCHEMA.SBLD_ADD_TAG(REPO_VIEW_NAME => 'VW_Customer_BASE',
TAG => 'ROLE,',
TAG_VALUE => 'PUBLIC',
TAG_CLASS => 'USAGE',
TAG_COMMENT => 'Required Role',
COPY_CONTROL =>
'COPY') ;
--
-- Show AtlasQL Tagas
--
SELECT * FROM TABLE (ASQL_DB.ASDF_SCHEMA.SBLD_SHOW_TAGS (REPO_VIEW_NAME => 'VW_Customer_BASE'));
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_GET_TAG_VALUE(REPO_VIEW_NAME => 'VW_Customer_BASE', TAG => 'ROLE', TAG_CLASS => 'USAGE') ;
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_GET_QUOTED_TAG_VALUE(REPO_VIEW_NAME => 'VW_Customer_BASE', TAG => 'ROLE', TAG_CLASS => 'USAGE') ;
--
-- Add Exression
--
SELECT
ASQL_DB.ASDF_SCHEMA.SBLD_ADD_EXPRESSION(
REPO_VIEW_NAME => 'VW_Invoice_BASE',
NEW_COLUMN_NAME => 'Commisson',
EXPRESSION =>
$$CASE
WHEN "Total" > 20
THEN CAST ("Total" * 0.1 AS NUMBER(8,2))
WHEN "Total" > 13
THEN CAST ("Total" * 0.075 AS NUMBER(8,2))
WHEN "Total" > 5
THEN CAST ("Total" * 0.05 AS NUMBER(8,2))
ELSE CAST ("Total" * 0.02 AS NUMBER(8,2))
END$$,
COLUMN_ORDER => 20,
EXPRESSION_COMMENT => 'Calculate Commission on Sales');
--
-- Show expression
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_SELECT ('VW_Invoice_BASE');
--
-- Create Single New VIEW Definitions
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_NEW_VIEW_COPY_FROM_REPO_VIEW(
NEW_REPO_VIEW_NAME => 'VW_INVOICE_NL',
FROM_REPO_VIEW_NAME => 'VW_Invoice_BASE');
---
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_SELECT('VW_INVOICE_NL');
---
-- Bulk Create
--
SELECT LISTAGG(
ASQL_DB.ASDF_SCHEMA.SBLD_NEW_VIEW_COPY_FROM_REPO_VIEW(
NEW_REPO_VIEW_NAME => REPLACE(UPPER(VIEW_NAME),'BASE','NL'),
FROM_REPO_VIEW_NAME => VIEW_NAME),' ')
FROM
(SELECT DISTINCT VIEW_NAME
FROM
IDENTIFIER($SBLDR_META_REPOSITORY)
WHERE VIEW_NAME LIKE 'VW%BASE' AND VIEW_NAME NOT IN ('VW_Invoice_BASE','VW_INVOICE_NL'));
--
-- Rename individual column
--
SELECT
ASQL_DB.ASDF_SCHEMA.SBLD_ALTER_VIEW_RENAME_COLUMN( REPO_VIEW_NAME => 'VW_CUSTOMER_NL',
VIEW_COLUMN => 'LastName', NEW_NAME => SBLD_CONVERT_TO_MIXED_SPACE( 'LastName') );
--
select view_column from identifier($sbldr_meta_repository) where view_name = 'VW_CUSTOMER_NL' and source_column_data_type not ilike 'SBLDR_TAG';
--
-- Rename all columns
--
--
SELECT
LISTAGG(
ASQL_DB.ASDF_SCHEMA.SBLD_ALTER_VIEW_RENAME_ALL_COLUMNS (
REPO_VIEW_NAME => VIEW_NAME,
NEW_NAME =>
$$'CASE WHEN VIEW_COLUMN LIKE '%Id'
THEN VIEW_COLUMN
ELSE
ASQL_DB.ASDF_SCHEMA.SBLD_CONVERT_TO_MIXED_SPACE(
view_column) END'$$ ),' ')
FROM
(SELECT
DISTINCT view_name
FROM
IDENTIFIER($SBLDR_META_REPOSITORY)
WHERE
view_name LIKE '%_NL');
-- Copy column
--
SELECT
ASQL_DB.ASDF_SCHEMA.SBLD_COPY_VIEW_COLUMN(
REPO_VIEW_NAME =>'VW_CUSTOMER_NL',
VIEW_COLUMN => 'CustomerId',
NEW_VIEW_COLUMN => 'Customer Account Number');
--
-- Show new column
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_SELECT('VW_CUSTOMER_NL');
--
-- Change Qualification for VW_CUSTOMER_NL
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_ALTER_SOURCE_OBJECT_QUALIFICATION(
REPO_VIEW_NAME => 'VW_CUSTOMER_NL',
SOURCE_OBJECT => 'Customer',
SOURCE_DB => $SOURCE_DB,
SOURCE_SCHEMA => $SOURCE_SCHEMA,
SOURCE_TABLE_ALIAS => 'NULL',
NEW_SOURCE_DB => 'NULL',
NEW_SOURCE_SCHEMA => 'NULL',
NEW_TABLE_ALIAS => 'CUST_' ) ;
---
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_SELECT('VW_CUSTOMER_NL');
-- Change others to OBJECT qualification
--
SELECT
ASQL_DB.ASDF_SCHEMA.SBLD_ALTER_SOURCE_OBJECT_QUALIFICATION( REPO_VIEW_NAME => VIEW_NAME,
SOURCE_OBJECT => source_object, SOURCE_DB => $SOURCE_DB, SOURCE_SCHEMA => $SOURCE_SCHEMA,
SOURCE_TABLE_ALIAS => 'NULL', NEW_SOURCE_DB => 'NULL', NEW_SOURCE_SCHEMA => 'NULL'
, NEW_TABLE_ALIAS => SOURCE_OBJECT )
FROM
( SELECT
DISTINCT VIEW_NAME,
SOURCE_OBJECT
FROM
IDENTIFIER($SBLDR_META_REPOSITORY)
WHERE
VIEW_NAME ilike '%NL' AND VIEW_NAME <> 'VW_CUSTOMER_NL');
--
SELECT ASQL_DB.ASDF_SCHEMA.SBLD_SELECT('VW_INVOICE_NL');
--
#AltaSQL #Snowflake #SQLAutomation #DataProductivity #MetadataManagement