DDL stands for “Data Definition Language” and relates to creating and modifying catalog objects such as Tables.
Create catalog with specified name.
-- create catalog cat CREATE DATABASE cat;
Create schema under specified catalog, or the default DataFusion catalog if not specified.
-- create schema emu under catalog cat CREATE SCHEMA cat.emu;
CREATE EXTERNAL TABLE SQL statement registers a location on a local file system or remote object store as a named table which can be queried.
The supported syntax is:
CREATE [UNBOUNDED] EXTERNAL TABLE [ IF NOT EXISTS ] <TABLE_NAME>[ (<column_definition>) ] STORED AS <file_type> [ PARTITIONED BY (<column list>) ] [ WITH ORDER (<ordered column list>) ] [ OPTIONS (<key_value_list>) ] LOCATION <literal> <column_definition> := (<column_name> <data_type>, ...) <column_list> := (<column_name>, ...) <ordered_column_list> := (<column_name> <sort_clause>, ...) <key_value_list> := (<literal> <literal, <literal> <literal>, ...)
For a comprehensive list of format-specific options that can be specified in the OPTIONS clause, see Format Options.
file_type is one of CSV, ARROW, PARQUET, AVRO or JSON
LOCATION <literal> specifies the location to find the data. It can be a path to a file or directory of partitioned files locally or on an object store.
Parquet data sources can be registered by executing a CREATE EXTERNAL TABLE SQL statement such as the following. It is not necessary to provide schema information for Parquet files.
CREATE EXTERNAL TABLE taxi STORED AS PARQUET LOCATION '/mnt/nyctaxi/tripdata.parquet';
:::{note} Statistics : By default, when a table is created, DataFusion will read the files to gather statistics, which can be expensive but can accelerate subsequent queries substantially. If you don't want to gather statistics when creating a table, set the datafusion.execution.collect_statistics configuration option to false before creating the table. For example:
SET datafusion.execution.collect_statistics = false;
See the config settings docs for more details. :::
CSV data sources can also be registered by executing a CREATE EXTERNAL TABLE SQL statement. The schema will be inferred based on scanning a subset of the file.
CREATE EXTERNAL TABLE test STORED AS CSV LOCATION '/path/to/aggregate_simple.csv' OPTIONS ('has_header' 'true');
It is also possible to use compressed files, such as .csv.gz:
CREATE EXTERNAL TABLE test STORED AS CSV COMPRESSION TYPE GZIP LOCATION '/path/to/aggregate_simple.csv.gz' OPTIONS ('has_header' 'true');
It is also possible to specify the schema manually.
CREATE EXTERNAL TABLE test ( c1 VARCHAR NOT NULL, c2 INT NOT NULL, c3 SMALLINT NOT NULL, c4 SMALLINT NOT NULL, c5 INT NOT NULL, c6 BIGINT NOT NULL, c7 SMALLINT NOT NULL, c8 INT NOT NULL, c9 BIGINT NOT NULL, c10 VARCHAR NOT NULL, c11 FLOAT NOT NULL, c12 DOUBLE NOT NULL, c13 VARCHAR NOT NULL ) STORED AS CSV LOCATION '/path/to/aggregate_test_100.csv' OPTIONS ('has_header' 'true');
It is also possible to specify a directory that contains a partitioned table (multiple files with the same schema)
CREATE EXTERNAL TABLE test STORED AS CSV LOCATION '/path/to/directory/of/files' OPTIONS ('has_header' 'true');
Tables that are partitioned using a Hive compliant partitioning scheme will have their columns and values automatically detected and incorporated into the table's schema and data. Given the following example directory structure:
hive_partitioned/ ├── a=1 │ └── b=200 │ └── file1.parquet └── a=2 └── b=100 └── file2.parquet
Users can specify the top level hive_partitioned directory as an EXTERNAL TABLE and leverage the Hive partitions to query and filter data.
CREATE EXTERNAL TABLE hive_partitioned STORED AS PARQUET LOCATION '/path/to/hive_partitioned/'; SELECT count(*) FROM hive_partitioned WHERE b=100; +------------------+ | count(*) | +------------------+ | 1 | +------------------+
We can create unbounded data sources using the CREATE UNBOUNDED EXTERNAL TABLE SQL statement.
CREATE UNBOUNDED EXTERNAL TABLE taxi STORED AS PARQUET LOCATION '/mnt/nyctaxi/tripdata.parquet';
Note that this statement actually reads data from a fixed-size file, so a better example would involve reading from a FIFO file. Nevertheless, once Datafusion sees the UNBOUNDED keyword in a data source, it tries to execute queries that refer to this unbounded source in streaming fashion. If this is not possible according to query specifications, plan generation fails stating it is not possible to execute given query in streaming fashion. Note that queries that can run with unbounded sources (i.e. in streaming mode) are a subset of those that can with bounded sources. A query that fails with unbounded source(s) may work with bounded source(s).
WITH ORDER ClauseWhen creating an output from a data source that is already ordered by an expression, you can pre-specify the order of the data using the WITH ORDER clause. This applies even if the expression used for sorting is complex, allowing for greater flexibility.
Here's an example of how to use WITH ORDER clause.
CREATE EXTERNAL TABLE test ( c1 VARCHAR NOT NULL, c2 INT NOT NULL, c3 SMALLINT NOT NULL, c4 SMALLINT NOT NULL, c5 INT NOT NULL, c6 BIGINT NOT NULL, c7 SMALLINT NOT NULL, c8 INT NOT NULL, c9 BIGINT NOT NULL, c10 VARCHAR NOT NULL, c11 FLOAT NOT NULL, c12 DOUBLE NOT NULL, c13 VARCHAR NOT NULL ) STORED AS CSV WITH ORDER (c2 ASC, c5 + c8 DESC NULLS FIRST) LOCATION '/path/to/aggregate_test_100.csv' OPTIONS ('has_header' 'true');
Where WITH ORDER clause specifies the sort order:
WITH ORDER (sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }] [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...])
It's important to understand that using the WITH ORDER clause in the CREATE EXTERNAL TABLE statement only specifies the order in which the data should be read from the external file. If the data in the file is not already sorted according to the specified order, then the results may not be correct.
It's also important to note that the WITH ORDER clause does not affect the ordering of the data in the original external file.
If data sources are already partitioned in Hive style, PARTITIONED BY can be used for partition pruning.
/mnt/nyctaxi/year=2022/month=01/tripdata.parquet /mnt/nyctaxi/year=2021/month=12/tripdata.parquet /mnt/nyctaxi/year=2021/month=11/tripdata.parquet
CREATE EXTERNAL TABLE taxi STORED AS PARQUET PARTITIONED BY (year, month) LOCATION '/mnt/nyctaxi';
An in-memory table can be created with a query or values list.
CREATE TABLE IF NOT EXISTS valuetable AS VALUES(1,'HELLO'),(12,'DATAFUSION'); CREATE TABLE IF NOT EXISTS valuetable(c1 INT, c2 VARCHAR) AS VALUES(1,'HELLO'),(12,'DATAFUSION'); CREATE TABLE memtable as select * from valuetable;
Removes the table from DataFusion's catalog.
CREATE TABLE users AS VALUES(1,2),(2,3); DROP TABLE users; -- or use 'if exists' to silently ignore if the table doesn't exist DROP TABLE IF EXISTS nonexistent_table;
View is a virtual table based on the result of a SQL query. It can be created from an existing table or values list.
CREATE TABLE users AS VALUES(1,2),(2,3),(3,4),(4,5); CREATE VIEW test AS SELECT column1 FROM users; SELECT * FROM test; +---------+ | column1 | +---------+ | 1 | | 2 | | 3 | | 4 | +---------+
CREATE VIEW test AS VALUES(1,2),(5,6); SELECT * FROM test; +---------+---------+ | column1 | column2 | +---------+---------+ | 1 | 2 | | 5 | 6 | +---------+---------+
Removes the view from DataFusion's catalog.
-- drop users_v view from the customer_a schema DROP VIEW IF EXISTS customer_a.users_v;
Displays the schema of a table, showing column names, data types, and nullable status. Both DESCRIBE and DESC are supported as aliases.
The output contains three columns:
column_name: The name of the columndata_type: The data type of the column (e.g., Int32, Utf8, Boolean)is_nullable: Whether the column can contain null values (YES/NO)-- Create a table CREATE TABLE users AS VALUES (1, 'Alice', true), (2, 'Bob', false); -- Describe the table structure DESCRIBE users;
Output:
+--------------+-----------+-------------+ | column_name | data_type | is_nullable | +--------------+-----------+-------------+ | column1 | Int64 | YES | | column2 | Utf8 | YES | | column3 | Boolean | YES | +--------------+-----------+-------------+
-- DESC is an alias for DESCRIBE DESC users;
-- Create an external table CREATE EXTERNAL TABLE taxi STORED AS PARQUET LOCATION '/mnt/nyctaxi/tripdata.parquet'; -- Describe its schema DESCRIBE taxi;
Output might show:
+--------------------+-----------------------------+-------------+ | column_name | data_type | is_nullable | +--------------------+-----------------------------+-------------+ | vendor_id | Int32 | YES | | pickup_datetime | Timestamp(Nanosecond, None) | NO | | passenger_count | Int32 | YES | | trip_distance | Float64 | YES | +--------------------+-----------------------------+-------------+
The DESCRIBE command works with all table types in DataFusion, including:
CREATE TABLECREATE EXTERNAL TABLECREATE VIEWDESCRIBE schema_name.table_name)