Description
Apache ORC is a columnar storage format widely used in the Hadoop ecosystem.Data types matching
The table below compares supported ORC data types and their corresponding ClickHouse data types inINSERT and SELECT queries.
- Other types are not supported.
- An ORC
Unioncolumn is read as a Variant over the union’s branch types, and aVariantcolumn is written as an ORCUnionover its branch types. Note thatVariantsorts its branch types, so the branch order may differ from the ORC file. Unions with duplicate branch types (e.g.uniontype<int,int>) are not supported. - Arrays can be nested and can have a value of the
Nullabletype as an argument.TupleandMaptypes also can be nested. - The data types of ClickHouse table columns do not have to match the corresponding ORC data fields. When inserting data, ClickHouse interprets data types according to the table above and then casts the data to the data type set for the ClickHouse table column.
DateTime64values between1969-12-31 23:59:59.001and1969-12-31 23:59:59.999999999(UTC) are written as1970-01-01 00:00:00. This is a gap in the ORC specification, which stores the seconds and the nanoseconds of a timestamp, with the nanoseconds not negative. The reference implementations add one second to a negative timestamp with more than 999999 nanoseconds, and the readers subtract it, but a timestamp between -1 and 0 seconds would then be stored as 0 seconds and read back after the epoch. The implementations write such a timestamp differently: the Java and C++ writers of Apache ORC write it so that it is read back one second later, and the Arrow ORC adapter (PyArrow) writes negative nanoseconds, which the specification does not allow and the Java reader rejects. ClickHouse writes the epoch instead, which all readers read back the same way.- When the writer and the reader time zones of a timestamp differ, the Java reader of Apache ORC and ClickHouse subtract that second before converting between the time zones, as the writers add it, while the C++ reader of Apache ORC (also used by PyArrow) subtracts it after the conversion. The C++ reader can then read timestamps before 1970 that are within the time zone offset of the epoch one second off, for the files of any writer.
Example usage
Inserting data
Using an ORC file with the following data, named asfootball.orc:
Reading data
Read data using theORC format:
Format settings
To exchange data with Hadoop, you can use HDFS table engine.