in datafusion/sql/src/parser.rs [1070:1540]
fn create_external_table() -> Result<(), DataFusionError> {
// positive case
let sql = "CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv'";
let display = None;
let name = ObjectName::from(vec![Ident::from("t")]);
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![make_column_def("c1", DataType::Int(display))],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: leading space
let sql = "CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv' ";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![make_column_def("c1", DataType::Int(None))],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: leading space + semicolon
let sql =
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv' ;";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![make_column_def("c1", DataType::Int(None))],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case with delimiter
let sql = "CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv' OPTIONS (format.delimiter '|')";
let display = None;
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![make_column_def("c1", DataType::Int(display))],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![(
"format.delimiter".into(),
Value::SingleQuotedString("|".into()),
)],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: partitioned by
let sql = "CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV PARTITIONED BY (p1, p2) LOCATION 'foo.csv'";
let display = None;
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![make_column_def("c1", DataType::Int(display))],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec!["p1".to_string(), "p2".to_string()],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: it is ok for sql stmt with `COMPRESSION TYPE GZIP` tokens
let sqls =
vec![
("CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv' OPTIONS
('format.compression' 'GZIP')", "GZIP"),
("CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv' OPTIONS
('format.compression' 'BZIP2')", "BZIP2"),
("CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv' OPTIONS
('format.compression' 'XZ')", "XZ"),
("CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV LOCATION 'foo.csv' OPTIONS
('format.compression' 'ZSTD')", "ZSTD"),
];
for (sql, compression) in sqls {
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![make_column_def("c1", DataType::Int(display))],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![(
"format.compression".into(),
Value::SingleQuotedString(compression.into()),
)],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
}
// positive case: it is ok for parquet files not to have columns specified
let sql = "CREATE EXTERNAL TABLE t STORED AS PARQUET LOCATION 'foo.parquet'";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![],
file_type: "PARQUET".to_string(),
location: "foo.parquet".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: it is ok for parquet files to be other than upper case
let sql = "CREATE EXTERNAL TABLE t STORED AS parqueT LOCATION 'foo.parquet'";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![],
file_type: "PARQUET".to_string(),
location: "foo.parquet".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: it is ok for avro files not to have columns specified
let sql = "CREATE EXTERNAL TABLE t STORED AS AVRO LOCATION 'foo.avro'";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![],
file_type: "AVRO".to_string(),
location: "foo.avro".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: it is ok for avro files not to have columns specified
let sql =
"CREATE EXTERNAL TABLE IF NOT EXISTS t STORED AS PARQUET LOCATION 'foo.parquet'";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![],
file_type: "PARQUET".to_string(),
location: "foo.parquet".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: true,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: column definition allowed in 'partition by' clause
let sql =
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV PARTITIONED BY (p1 int) LOCATION 'foo.csv'";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![
make_column_def("c1", DataType::Int(None)),
make_column_def("p1", DataType::Int(None)),
],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec!["p1".to_string()],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// negative case: mixed column defs and column names in `PARTITIONED BY` clause
let sql =
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV PARTITIONED BY (p1 int, c1) LOCATION 'foo.csv'";
expect_parse_error(
sql,
"SQL error: ParserError(\"Expected: a data type name, found: ) at Line: 1, Column: 73\")",
);
// negative case: mixed column defs and column names in `PARTITIONED BY` clause
let sql =
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV PARTITIONED BY (c1, p1 int) LOCATION 'foo.csv'";
expect_parse_error(sql, "SQL error: ParserError(\"Expected: ',' or ')' after partition definition, found: int at Line: 1, Column: 70\")");
// positive case: additional options (one entry) can be specified
let sql =
"CREATE EXTERNAL TABLE t STORED AS x OPTIONS ('k1' 'v1') LOCATION 'blahblah'";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![],
file_type: "X".to_string(),
location: "blahblah".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![("k1".into(), Value::SingleQuotedString("v1".into()))],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// positive case: additional options (multiple entries) can be specified
let sql =
"CREATE EXTERNAL TABLE t STORED AS x OPTIONS ('k1' 'v1', k2 v2) LOCATION 'blahblah'";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![],
file_type: "X".to_string(),
location: "blahblah".into(),
table_partition_cols: vec![],
order_exprs: vec![],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![
("k1".into(), Value::SingleQuotedString("v1".into())),
("k2".into(), Value::SingleQuotedString("v2".into())),
],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// Ordered Col
let sqls = ["CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 NULLS FIRST) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 NULLS LAST) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 ASC) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 DESC) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 DESC NULLS FIRST) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 DESC NULLS LAST) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 ASC NULLS FIRST) LOCATION 'foo.csv'",
"CREATE EXTERNAL TABLE t(c1 int) STORED AS CSV WITH ORDER (c1 ASC NULLS LAST) LOCATION 'foo.csv'"];
let expected = vec![
(None, None),
(None, Some(true)),
(None, Some(false)),
(Some(true), None),
(Some(false), None),
(Some(false), Some(true)),
(Some(false), Some(false)),
(Some(true), Some(true)),
(Some(true), Some(false)),
];
for (sql, (asc, nulls_first)) in sqls.iter().zip(expected.into_iter()) {
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![make_column_def("c1", DataType::Int(None))],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![vec![OrderByExpr {
expr: Identifier(Ident {
value: "c1".to_owned(),
quote_style: None,
span: Span::empty(),
}),
options: OrderByOptions { asc, nulls_first },
with_fill: None,
}]],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
}
// Ordered Col
let sql = "CREATE EXTERNAL TABLE t(c1 int, c2 int) STORED AS CSV WITH ORDER (c1 ASC, c2 DESC NULLS FIRST) LOCATION 'foo.csv'";
let display = None;
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![
make_column_def("c1", DataType::Int(display)),
make_column_def("c2", DataType::Int(display)),
],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![vec![
OrderByExpr {
expr: Identifier(Ident {
value: "c1".to_owned(),
quote_style: None,
span: Span::empty(),
}),
options: OrderByOptions {
asc: Some(true),
nulls_first: None,
},
with_fill: None,
},
OrderByExpr {
expr: Identifier(Ident {
value: "c2".to_owned(),
quote_style: None,
span: Span::empty(),
}),
options: OrderByOptions {
asc: Some(false),
nulls_first: Some(true),
},
with_fill: None,
},
]],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// Ordered Binary op
let sql = "CREATE EXTERNAL TABLE t(c1 int, c2 int) STORED AS CSV WITH ORDER (c1 - c2 ASC) LOCATION 'foo.csv'";
let display = None;
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![
make_column_def("c1", DataType::Int(display)),
make_column_def("c2", DataType::Int(display)),
],
file_type: "CSV".to_string(),
location: "foo.csv".into(),
table_partition_cols: vec![],
order_exprs: vec![vec![OrderByExpr {
expr: Expr::BinaryOp {
left: Box::new(Identifier(Ident {
value: "c1".to_owned(),
quote_style: None,
span: Span::empty(),
})),
op: BinaryOperator::Minus,
right: Box::new(Identifier(Ident {
value: "c2".to_owned(),
quote_style: None,
span: Span::empty(),
})),
},
options: OrderByOptions {
asc: Some(true),
nulls_first: None,
},
with_fill: None,
}]],
if_not_exists: false,
temporary: false,
unbounded: false,
options: vec![],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// Most complete CREATE EXTERNAL TABLE statement possible
let sql = "
CREATE UNBOUNDED EXTERNAL TABLE IF NOT EXISTS t (c1 int, c2 float)
STORED AS PARQUET
WITH ORDER (c1 - c2 ASC)
PARTITIONED BY (c1)
LOCATION 'foo.parquet'
OPTIONS ('format.compression' 'zstd',
'format.delimiter' '*',
'ROW_GROUP_SIZE' '1024',
'TRUNCATE' 'NO',
'format.has_header' 'true')";
let expected = Statement::CreateExternalTable(CreateExternalTable {
name: name.clone(),
columns: vec![
make_column_def("c1", DataType::Int(None)),
make_column_def("c2", DataType::Float(None)),
],
file_type: "PARQUET".to_string(),
location: "foo.parquet".into(),
table_partition_cols: vec!["c1".into()],
order_exprs: vec![vec![OrderByExpr {
expr: Expr::BinaryOp {
left: Box::new(Identifier(Ident {
value: "c1".to_owned(),
quote_style: None,
span: Span::empty(),
})),
op: BinaryOperator::Minus,
right: Box::new(Identifier(Ident {
value: "c2".to_owned(),
quote_style: None,
span: Span::empty(),
})),
},
options: OrderByOptions {
asc: Some(true),
nulls_first: None,
},
with_fill: None,
}]],
if_not_exists: true,
temporary: false,
unbounded: true,
options: vec![
(
"format.compression".into(),
Value::SingleQuotedString("zstd".into()),
),
(
"format.delimiter".into(),
Value::SingleQuotedString("*".into()),
),
(
"ROW_GROUP_SIZE".into(),
Value::SingleQuotedString("1024".into()),
),
("TRUNCATE".into(), Value::SingleQuotedString("NO".into())),
(
"format.has_header".into(),
Value::SingleQuotedString("true".into()),
),
],
constraints: vec![],
});
expect_parse_ok(sql, expected)?;
// For error cases, see: `create_external_table.slt`
Ok(())
}