SHOW CREATE command

The page shows you how to use the SHOW CREATE command to view the syntax used to create an entity within Upsolver.

To view the syntax used to create an entity within Upsolver, run the SHOW CREATE command for a table, job, materialized view, or cluster. This command returns the full syntax that was executed in Upsolver, including default options appended by the query engine.

You can run the command for entities that you have written and created, as well as entities created by Upsolver:

SHOW CREATE { TABLE <table_name> 
            | JOB <job_name>
            | MATERIALIZED VIEW <materialized_view_name>
            | CLUSTER <cluster_name> }           

After running the command, the results return the full syntax and you can copy and paste the code into your query window and re-use this code or adapt it as required. The results enable you to see the full settings applied to an entity when it was created.

If you have spaces in your entity names, ensure you enclose the name using double quotation marks.

Examples

SHOW CREATE TABLE

The following example runs the SHOW CREATE command for the sample table orders_raw_data, which is used to stage ingested data in the data lake:

 SHOW CREATE TABLE default_glue_catalog.upsolver_samples.orders_raw_data;

The results include all the details about the options used to create the table:

CREATE TABLE default_glue_catalog.upsolver_samples.orders_raw_data
PARTITION BY $event_date
GLOBALLY_UNIQUE_KEYS = false
STORAGE_CONNECTION = s3_connection
STORAGE_LOCATION = 's3://upsolver-managed-multi-tenant-env-us-east-1/multi-tenanat_<guid>/'
COMPUTE_CLUSTER = "Default Compute (Free)"
COMPRESSION = SNAPPY
COMPACTION_PROCESSES = 1
DISABLE_COMPACTION = false
EXPOSE_IN_CATALOG = true

Using this command you can quickly discover the table's computer cluster and storage information, as well as the columns used to partition the table.

SHOW CREATE JOB

The SHOW CREATE command can be run for any job within Upsolver. Being able to run this command from a query window means you don't need to drill into the job details to see the underlying script used to create it. Furthermore, you can run this for ingestion and transformation jobs, a job that generates sub-jobs, and a sub-job.

The following example displays the creation syntax for the load_raw_orders_to_staging job:

SHOW CREATE JOB load_raw_orders_to_staging;

This returns the following results:

CREATE SYNC JOB load_raw_orders_to_staging
CREATE_TABLE_IF_MISSING = false
COMPUTE_CLUSTER = "Default Compute (Free)"
RUN_PARALLELISM = 1
FILE_PATTERN = ''
DELETE_FILES_AFTER_LOAD = false
CONTENT_TYPE = (
TYPE = JSON
SPLIT_ROOT_ARRAY = true
STORE_JSON_AS_STRING = false
)
COMPRESSION = AUTO
AS COPY FROM S3 upsolver_s3_samples LOCATION = 's3://upsolver-samples/orders/'
INTO default_glue_catalog.upsolver_samples.orders_raw_data

SHOW CREATE MATERIALIZED VIEW

A materialized view is a type of job based on aggregated data. The view cannot be queried directly, only from within another job, so it's helpful to be able to display information about the SQL used to create it:

SHOW CREATE MATERIALIZED VIEW physical_store_orders_materialized_view;

This returns the results as below:

CREATE SYNC MATERIALIZED VIEW default_glue_catalog.upsolver_samples.physical_store_orders_materialized_view AS
SELECT orderid::STRING,
LAST(saleinfo.source::STRING) AS source,
LAST(saleinfo.store.location.country::STRING) AS country,
LAST(saleinfo.store.location.name::STRING) AS name,
LAST(saleinfo.store.servicedby.employeeid::BIGINT) AS employeeid,
LAST(saleinfo.store.servicedby.firstname::STRING) AS firstname,
LAST(saleinfo.store.servicedby.lastname::STRING) AS lastname
FROM default_glue_catalog.upsolver_samples.sales_info_raw_data
GROUP BY orderid::STRING
STORAGE_CONNECTION = s3_connection
STORAGE_LOCATION = 's3://upsolver-managed-multi-tenant-env-us-east-1/multi-tenanat_<guid>/'
COMPUTE_CLUSTER = "Default Compute (Free)"

SHOW CREATE CLUSTER

The example below returns all the information about the Default Compute (Free) cluster:

SHOW CREATE CLUSTER "Default Compute (Free)";

This command returns the following results for the default cluster:

CREATE COMPUTE CLUSTER "Default Compute (Free)"
COMMENT = 'Default Compute for unallocated-organization'
REGION = 'us-east-1'
STATIC_PUBLIC_IPS = 1
MIN_INSTANCES = 1
MAX_INSTANCES = 1
INSTANCE_TYPE_FAMILY = 'r6i.large'
SCALING_STRATEGY = LOW_LATENCY
ALLOW_MAINTENANCE_ACCESS = false
SPOT_OVERRIDE = ''