View Entity Syntax

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 = '';