What if create table schema not match CSV file schema?

[holicc]

I want to create an external table from a CSV file using SQL like this:

CREATE EXTERNAL TABLE IF NOT EXISTS region (
        r_regionkey BIGINT,
        r_name VARCHAR,
        r_comment VARCHAR,
        r_rev VARCHAR,
        customer_filed VARCHAR,
) STORED AS CSV LOCATION '../region.tbl' OPTIONS ('format.delimiter' '|', 'format.has_header' 'false');

When I select data from this table, I get an error: ArrowError(CsvError("incorrect number of fields for line 1, expected 5 got 4"), None)

I know the customer_filed field does not exist in the CSV file, but I need this field in the table. How can I achieve this?

[Jefffrey]

You can use the truncated_rows config:

datafusion/datafusion/common/src/config.rs

Lines 2792 to 2801 in 82181ac

	/// Whether to allow truncated rows when parsing, both within a single file and across files.
	///
	/// When set to false (default), reading a single CSV file which has rows of different lengths will
	/// error; if reading multiple CSV files with different number of columns, it will also fail.
	///
	/// When set to true, reading a single CSV file with rows of different lengths will pad the truncated
	/// rows with null values for the missing columns; if reading multiple CSV files with different number
	/// of columns, it creates a union schema containing all columns found across the files, and will
	/// pad any files missing columns with null values for their rows.
	pub truncated_rows: Option<bool>, default = None

For example:

datafusion-cli (main)$ cat ~/Downloads/test.csv
name_1,num,"Column Name"
andrew,100,1
jorge,200,2
andy,150,3
paul,300,4
datafusion-cli (main)$ cargo run
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.13s
     Running `/Users/jeffrey/.cargo_target_cache/debug/datafusion-cli`
DataFusion CLI v51.0.0
> CREATE EXTERNAL TABLE IF NOT EXISTS test (
    name_1 VARCHAR,
    num INT,
    "Column Name" INT,
    col123 INT,
) STORED AS CSV LOCATION '/Users/jeffrey/Downloads/test.csv' OPTIONS (
    'format.delimiter' ',',
    'format.truncated_rows' 'true',
    'format.has_header' 'true'
);
0 row(s) fetched.
Elapsed 0.037 seconds.

> select * from test;
+--------+-----+-------------+--------+
| name_1 | num | Column Name | col123 |
+--------+-----+-------------+--------+
| andrew | 100 | 1           | NULL   |
| jorge  | 200 | 2           | NULL   |
| andy   | 150 | 3           | NULL   |
| paul   | 300 | 4           | NULL   |
+--------+-----+-------------+--------+
4 row(s) fetched.
Elapsed 0.036 seconds.