OpenText Analytics Database (Vertica) is a columnar database, while Apache Parquet is an open-source, column-oriented data file format optimized for efficient data storage and retrieval. As a result, transferring data from Parquet to Vertica is much faster. However, there are instances where a copy statement may fail, resulting in the error detailed below.
ERROR 9400: Datatype mismatch: column 1 in the parquet source [/home/dbadmin/sample3.parquet] has type BYTE_ARRAY, expected int.
HINT: Check the allowed Vertica to Parquet type mappings
This brief runbook will guide you through troubleshooting similar scenarios. Allow me to illustrate with an example.
Let us create a table named test and load a parquet file using COPY Statement
eonv242=> create table test (i int, name varchar(30));
CREATE TABLE
eonv242=> copy test from '/home/dbadmin/sample3.parquet' parquet;
ERROR 9400: Datatype mismatch: column 1 in the parquet source [/home/dbadmin/sample3.parquet] has type BYTE_ARRAY, expected int.
HINT: Check the allowed Vertica to Parquet type mappings
It has been observed that the COPY statement failed to execute properly. To determine if this is due to a problem with the parquet file or a potential bug in Analytics Database, we need to inspect the metadata of the parquet file. Analytics Database features a built-in function called GET_METADATA() that can retrieve the metadata of parquet files. We shall now proceed to execute this function on the parquet file referenced in the COPY statement.
eonv242=> select get_metadata('/home/dbadmin/sample3.parquet');
get_metadata
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schema:
required group field_id=-1 duckdb_schema {
optional binary field_id=-1 column0 (String);
optional binary field_id=-1 column1 (String);
}
data page version:
data page v1
metadata:
{
"FileName": "/home/dbadmin/sample3.parquet",
"FileFormat": "Parquet",
"Version": "1.0",
"CreatedBy": "DuckDB",
"TotalRows": "1001",
"NumberOfRowGroups": "1",
"NumberOfRealColumns": "2",
"NumberOfColumns": "2",
"Columns": [
{ "Id": "0", "Name": "column0", "PhysicalType": "BYTE_ARRAY", "ConvertedType": "UTF8", "LogicalType": {"Type": "String"} },
{ "Id": "1", "Name": "column1", "PhysicalType": "BYTE_ARRAY", "ConvertedType": "UTF8", "LogicalType": {"Type": "String"} }
],
"RowGroups": [
{
"Id": "0", "TotalBytes": "0", "TotalCompressedBytes": "0", "Rows": "1001",
"ColumnChunks": [
{"Id": "0", "Values": "1001", "StatsSet": "True", "Stats": {"NumNulls": "0", "DistinctValues": "433", "Max": "first", "Min": "Aaron" },
"Compression": "GZIP", "Encodings": "PLAIN RLE_DICTIONARY ", "UncompressedSize": "0", "CompressedSize": "3688" },
{"Id": "1", "Values": "1001", "StatsSet": "True", "Stats": {"NumNulls": "0", "DistinctValues": "429", "Max": "Zimmerman", "Min": " last" },
"Compression": "GZIP", "Encodings": "PLAIN RLE_DICTIONARY ", "UncompressedSize": "0", "CompressedSize": "3857" }
]
}
]
}
(1 row)
After examining the Columns section, it was noted that two columns are designated as type String, whereas our table definition erroneously classified one of the columns as int. This reveals a problem with the table's definition.
Let us correct the table definition now and attempt to reload the same file. We should create a new table that specifies both columns as varchar to ensure they align with the parquet file's definition.
eonv242=> create table contacts (first_name varchar(20), last_name varchar(30));
CREATE TABLE
eonv242=> copy contacts from '/home/dbadmin/sample3.parquet' parquet;
Rows Loaded
-------------
1001
eonv242=> select * from contacts limit 1;
first_name | last_name
-------+------------
Aaron | Williamson
Ada | Black
Ada | Cunningham
Adele | Douglas
Alexander | Carroll
(5 rows)
We can confirm that the parquet file has been loaded successfully. For additional information, please refer to GET_METADATA function in vertica documentation.
