Best option to load a ~20GB zipped csv file

[KIC]

I have a pretty big .csv.gz file that I would like to COPY into a table. To my surprise, the documentation does not mention any other format than parquet to be loaded.

What is the best way to get this big file loaded into greptimedb? I have tried to convert this file to parquet using duckdb (fails memory error) and vaex (does not support timezoned timestamps). However, I have the feeling converting such a huge file might not be the best approach anyways.

Should I just stream plain insert statements to the psql console? looks like transactions are not implemented so I guess doing a record-wise auto-commit won't be fast either?

PS I have tried to trick the database by using TSDB protocol but since my "tags" would be numbers that doesn't work either.

EDIT: streaming inserts to pgsql would take approximately 245 days

[v0y4g3r]

Hi, the PostgresSQL protocol is not well optimized currently, so I think we can start from supporting CSV format in COPY FROM statement #1305

[KIC]

Thank you for opening a feature request! In the meantime, I managed to transform the csv into a parqet file. Sadly the COPY command doesn't work either. Maybe you can allow some `--force option to skip the schema compare?

Failed to execute query: COPY yf_quote FROM '/share/yf_quote.parquet' WITH (FORMAT = 'parquet');, source: Failed to execute statement, source: Failure during query execution, source: File Schema mismatch,
expected table schema: Field { name: "symbol", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "date", data_type: Timestamp(Millisecond, None),        nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {"greptime:time_index": "true"} }, Field { name: "open", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "high", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "low", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "close", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "volume", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "dividends", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "stockSplits", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }
            but found :Field { name: "symbol", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "date", data_type: Timestamp(Microsecond, Some("UTC")), nullable: true,  dict_id: 0, dict_is_ordered: false, metadata: {} },                              Field { name: "open", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "high", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "low", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "close", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "volume", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "dividends", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "stockSplits", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }

[v0y4g3r]

Time zone will be supported by this week, I think by then you can directly import that file.

[v0y4g3r]

I believe the latest build can now fix the problem, you can try importing large parquet files using COPY FROM.
I did a simple test to copy 50 million rows and it takes about 5mins.

mysql> copy demo from '/Users/lei/parquet/tz.parquet';
Query OK, 50000000 rows affected (5 min 3.49 sec)

Feel free to reopen this discussion if you encounter any problem.