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. It is necessary to provide schema information for CSV files since DataFusion does not automatically infer the schema when using SQL to query CSV files.

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

CREATE MEMORY TABLE

Memory table can be created with query.

CREATE TABLE TABLE_NAME AS [SELECT | VALUES LIST]
CREATE TABLE valuetable AS VALUES(1,'HELLO'),(12,'DATAFUSION');

CREATE TABLE memtable as select * from valuetable;

DROP TABLE

The table can be deleted.

DROP TABLE [ IF EXISTS ] name
CREATE TABLE users AS VALUES(1,2),(2,3);

DROP TABLE users;