Export non-varchar data to CSV table using Trino (formerly PrestoDB)

user_956435 0
Viewed 1441

I am working on some benchmarks and need to compare ORC, Parquet and CSV formats. I have exported TPC/H (SF1000) to ORC based tables. When I want to export it to Parquet I can run:

CREATE TABLE hive.tpch_sf1_parquet.region
  WITH (format = 'parquet')
  AS SELECT * FROM hive.tpch_sf1_orc.region

When I try the similar approach with CSV, then I get the error Hive CSV storage format only supports VARCHAR (unbounded). I would assumed that it would convert the other datatypes (i.e. bigint) to text and store the column format in the Hive metadata.

I can export the data to CSV using trino --server trino:8080 --catalog hive --schema tpch_sf1_orc --output-format=CSV --execute 'SELECT * FROM nation, but then it gets emitted to a file. Although this works for SF1 it quickly becomes unusable for SF1000 scale-factor. Another disadvantage is that my Hive metastores wouldn't have the appropriate meta-data (although I could patch it manually if nothing else works).

Anyone an idea how to convert my ORC/Parquet data to CSV using Hive?

trino presto hive csv
2 Answers

In Trino Hive connector, the CSV table can contain varchar columns only.

You need to cast the exported columns to varchar when creating the table

CREATE TABLE region_csv
WITH (format='CSV')
AS SELECT CAST(regionkey AS varchar), CAST(name AS varchar), CAST(comment AS varchar)
FROM region_orc

Note that you will need to update your benchmark queries accordingly, e.g. by applying reverse casts.

user_654580

DISCLAIMER: Read the full post, before using anything discussed here. It's not real CSV and you migth screw up!

It is possible to create typed CSV-ish tables when using the TEXTFILE format and use ',' as the field separator:

CREATE TABLE hive.test.region (
  regionkey bigint,
  name varchar(25),
  comment varchar(152)
)
WITH (
  format = 'TEXTFILE',
  textfile_field_separator = ','
);

This will create a typed version of the table in the Hive catalog using the TEXTFILE format. It normally uses the ^A character (ASCII 10), but when set to ',' it resembles the same structure as CSV formats.

IMPORTANT: Although it looks like CSV, it is not real CSV. It doesn't follow RFC 4180, because it doesn't properly quote and escape. The following INSERT will not be inserted co:

INSERT INTO hive.test.region VALUES (
  1,
  'A "quote", with comma',
  'The comment contains a newline
in it');

The text will be copied unmodified to the file without escaping quotes or commas. This should have been written like this to be proper CSV:

1,"A ""quote"", with comma","The comment contains a newline
in it"

Unfortunately, it is written as:

1,A "quote", with comma,The comment contains a newline
in it

This results in invalid data that will be represented by NULL columns. For this reason, this method can only be used when you have full control over the text-based data and are sure that it doesn't contain newlines, quotes, commas, ...

user_9564350
Related