CREATE EXTERNAL TABLE Examples
Note: The column names in the VectorH external table must match the column names in the reference source.
1. Define an external table for a CSV data source:
CREATE EXTERNAL TABLE ext_csv (col1 INT4 NOT NULL, col2 VARCHAR(20) NOT NULL)
USING SPARK
WITH REFERENCE='hdfs:////namenode:8020/user/mark/file.csv'
OPTIONS=('delimiter' = '|', 'header' = 'true');
Note: CSV files do not retain schema information, so we recommend always reading or writing CSV files that have a header; otherwise, the name of the columns must match those in spark-csv, that is, C0, C1, .... If the option 'header' = 'true' is not specified and the Vector external table definition has different names, you will get an error like:
E_VW1213 External table provider reported an error
'org.apache.spark.sql.AnalysisException: cannot resolve 'a' given input columns C0, C1; line 1
pos 38'.
2. Define an external table for an ORC data source:
CREATE EXTERNAL TABLE my_table_orc(a INT8 NOT NULL)
USING SPARK WITH REFERENCE='hdfs://hornet:8020/user/mark/my_table.orc';