DDL

CREATE EXTERNAL TABLE

Parquet data sources can be registered by executing a CREATE EXTERNAL TABLE SQL statement. It is not necessary to provide schema information for Parquet files.

CREATE EXTERNAL TABLE taxi
STORED AS PARQUET
LOCATION '/mnt/nyctaxi/tripdata.parquet';

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
WITH HEADER ROW
LOCATION '/path/to/aggregate_simple.csv';

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
WITH HEADER ROW
LOCATION '/path/to/aggregate_test_100.csv';

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

CREATE TABLE

An in-memory table can be created with a query or values list.

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] table_name AS [SELECT | VALUES LIST];
CREATE TABLE valuetable IF NOT EXISTS AS VALUES(1,'HELLO'),(12,'DATAFUSION');

CREATE TABLE memtable as select * from valuetable;

DROP TABLE

Removes the table from DataFusion’s catalog.

DROP TABLE [ IF EXISTS ] table_name;
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;

DROP VIEW

Removes the view from DataFusion’s catalog.

DROP VIEW [ IF EXISTS ] view_name;
-- drop users_v view from the customer_a schema
DROP VIEW IF EXISTS customer_a.users_v;