GENERATE_COLUMN_DESCRIPTION
Generates a list of columns from a set of staged files that contain semi-structured data using the
INFER_SCHEMA function output.
The output from this function can be used as input when manually creating a table, external table, Apache Iceberg™ table, or view (using the appropriate
CREATE <object> command) based on the column definitions of the staged files.
Alternatively, the CREATE TABLE or CREATE ICEBERG TABLE command with the USING TEMPLATE clause can be used to create a new table with the
column definitions derived from the same INFER_SCHEMA function output.
Examples
Detect, format, and output the set of column definitions in a set of Parquet files staged in the mystage stage. The output columns are
formatted for creating a table.
This example builds on an example in the INFER_SCHEMA topic:
-- Create a file format that sets the file type as Parquet.
CREATE FILE FORMAT my_parquet_format
TYPE = parquet;
-- Query the GENERATE_COLUMN_DESCRIPTION function.
SELECT GENERATE_COLUMN_DESCRIPTION(ARRAY_AGG(OBJECT_CONSTRUCT(*)), 'table') AS COLUMN_DESCRIPTION
FROM TABLE (
INFER_SCHEMA(
LOCATION=>'@mystage',
FILE_FORMAT=>'my_parquet_format'
)
);
+--------------------+
| COLUMN_DESCRIPTION |
|--------------------|
| "country" VARIANT, |
| "continent" TEXT |
+--------------------+
-- The function output can be used to define the columns in a table.
CREATE TABLE mytable ("country" VARIANT, "continent" TEXT);
Same as the previous example, but generates a set of columns formatted for creating an external table:
-- Query the GENERATE_COLUMN_DESCRIPTION function.
SELECT GENERATE_COLUMN_DESCRIPTION(ARRAY_AGG(OBJECT_CONSTRUCT(*)), 'external_table') AS COLUMN_DESCRIPTION
FROM TABLE (
INFER_SCHEMA(
LOCATION=>'@mystage',
FILE_FORMAT=>'my_parquet_format'
)
);
+---------------------------------------------+
| COLUMN_DESCRIPTION |
|---------------------------------------------|
| "country" VARIANT AS ($1:country::VARIANT), |
| "continent" TEXT AS ($1:continent::TEXT) |
+---------------------------------------------+
Same as the previous examples, but generates a set of columns formatted for creating an Iceberg table:
-- Create a file format that sets the file type as Parquet.
CREATE OR REPLACE FILE FORMAT my_parquet_format
TYPE = PARQUET
USE_VECTORIZED_SCANNER = TRUE;
-- Query the GENERATE_COLUMN_DESCRIPTION function.
SELECT GENERATE_COLUMN_DESCRIPTION(ARRAY_AGG(OBJECT_CONSTRUCT(*)), 'table') AS COLUMN_DESCRIPTION
FROM TABLE (
INFER_SCHEMA(
LOCATION=>'@my_int_stage',
FILE_FORMAT=>'my_parquet_format',
KIND => 'ICEBERG'
)
);
+---------------------------------------------+
| COLUMN_DESCRIPTION |
|---------------------------------------------|
| "id" INT NOT NULL, |
| "custnum" INT NOT NULL |
+---------------------------------------------+
Same as the previous examples, but generates a set of columns formatted for creating a view:
-- Query the GENERATE_COLUMN_DESCRIPTION function.
SELECT GENERATE_COLUMN_DESCRIPTION(ARRAY_AGG(OBJECT_CONSTRUCT(*)), 'view') AS COLUMN_DESCRIPTION
FROM TABLE (
INFER_SCHEMA(
LOCATION=>'@mystage',
FILE_FORMAT=>'my_parquet_format'
)
);
+--------------------+
| COLUMN_DESCRIPTION |
|--------------------|
| "country" , |
| "continent" |
+--------------------+
The following example sets INFER_NESTED_TYPES => TRUE to return structured type definitions for the nested columns in a set of
staged Parquet files:
-- Generate column definitions from INFER_SCHEMA output that uses INFER_NESTED_TYPES => TRUE.
SELECT GENERATE_COLUMN_DESCRIPTION(ARRAY_AGG(OBJECT_CONSTRUCT(*)), 'table') AS COLUMN_DESCRIPTION
FROM TABLE (
INFER_SCHEMA(
LOCATION=>'@mystage/orders/',
FILE_FORMAT=>'my_parquet_format',
INFER_NESTED_TYPES=>TRUE
)
);
+----------------------------------------------------------------+
| COLUMN_DESCRIPTION |
|----------------------------------------------------------------|
| "name" TEXT, |
| "address" OBJECT(city TEXT, zip NUMBER(10, 0)), |
| "tags" ARRAY(TEXT), |
| "metadata" MAP(TEXT, BOOLEAN) |
+----------------------------------------------------------------+
For more information, see
Choose column types.
Note
Using * for ARRAY_AGG(OBJECT_CONSTRUCT()) might result in an error if the returned result is larger
than 128 MB. Avoid using * for larger result sets, and only use the required columns, COLUMN NAME,
TYPE, and NULLABLE, for the query. You can include the optional column ORDER_ID when using
WITHIN GROUP (ORDER BY order_id).