CREATE EXTERNAL TABLE (X100 Only)
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE EXTERNAL TABLE statement maps the structure of a data file created outside of Actian Ingres to the structure of a Actian Ingres table. The data can then be queried from its original locations.
This statement has the following format:
CREATE EXTERNAL TABLE [IF NOT EXISTS] [schema.]table_name
(column_name data_type {,column_name data_type})
USING provider_name
WITH REFERENCE='reference'
[,FORMAT='format']
[,OPTIONS=('key'=value {,'key'='value'})]
IF NOT EXISTS
Creates the table if it does not exist and returns without error if the table already exists.
Note: Use this option with caution. The table definition of a preexisting table may differ from that of the CREATE EXTERNAL TABLE IF NOT EXISTS statement.
table_name
Defines the name of the external table to be created.
column_name data_type
Specifies the column name and data type of each column. Each column specification must be separated with a comma.
USING provider_name
Specifies the name of the provider. The only valid provider is SPARK.
Provider Name Application
SPARK Spark
WITH REFERENCE='reference'
(Required) Specifies the reference to the external datasource. This corresponds to the parameter passed to the load method of DataFrameReader or the save method of DataFrameWriter. For example, in case of a database system, the reference string matches the source table name.
Depending on the FORMAT, the REFERENCE can now contain multiple SparkSQL statements (separated by ';') OR Scala script code. Only temporary schema elements like temporary views, tables or functions can be created within the SparkSQL or Scala snippets. Persistent Spark catalog items are not allowed. The schema of the output of the last SparkSQL or Scala statement must match the schema of the external table definition. The user is not allowed to create a new Spark session.
A pre-allocated Spark session exists and can be used via parameter "spark".
FORMAT='format'
(Optional) Is a WITH clause option that specifies the format of the external data.
Example formats include: csv, avro, parquet, hive, orc, json, jdbc. For other datasources, format corresponds to the class name that defines that external datasource. For example, for Redshift it would be com.databricks.spark.redshift. This corresponds to the parameter passed to the format method of DataFrameReader/Writer.
When FORMAT is not specified, the Spark-Vector Provider tries to recognize the format for files by looking at the file extension.
Enabling "Advanced Staging Tables" requires setting the format either to 'Scala' or 'SparkSQL', depending on the code snippet provided as REFERENCE string.
OPTIONS
(Optional) Is a WITH clause option that specifies user defined options for the datasource read from or written to. The options are not Actian Ingres-specific; they are given by Spark and correspond to the options method of the DataFrameReader/Writer. For example, for CSV files you can pass any options supported by spark-csv. For additional options supported by various external datasources, see the DataFrameReader documentation at
https://spark.apache.org/docs/latest/api/java/org/apache/spark/sql/DataFrameReader.html.
Useful options are:
'HEADER'='TRUE' | 'FALSE'
Indicates whether the data file contains a header row.
Note: When referencing a CSV file, a header is included by default.
'DELIMITER'='character'
Indicates the character used in the data file as the record delimiter. Example: 'delimiter'='|'
'SCHEMA'='schema'
Specifies the table column definitions of the source using SparkSQL types. We recommend specifying this if the source file being loaded does not contain a header row. If not specified, Spark tries to infer the schema, which can degrade performance.
Note: The inferSchema=true option is ignored to avoid implicit casting of data types while parsing (CSV files, for example), since it can lead to unexpected results. Hence, if you do not specify a schema under OPTIONS for the source file, the schema of the external table will be used for file parsing. If you require casting of data types from the source file to the external table schema, use one of the following options: (1) Specify the source schema under OPTIONS (be aware that this implies implicit type casting to the external table schema by Spark). (2) For Spark Provider 3, use the staging table to explicitly cast data types. (3) Cast the data types in Actian Ingres.
'STAGING'='SparkSQL query string'
Specifies a SparkSQL query (
https://spark.apache.org/docs/latest/sql-ref.html) that creates an intermediate table in Spark for cleaning data or mapping schema on top of the source data before the data is transferred into Actian Ingres. It has the form of SELECT...FROM THIS_TABLE, where THIS_TABLE is a designated keyword. The schema of the staging table must exactly match the schema of the external table. For details, see the staging table examples in
CREATE EXTERNAL TABLE Examples.
'FILTER'='SparkSQL filter predicates string'
Predicate Pushdown
If a SQL query is issued in Actian Ingres to an EXTERNAL TABLE using Spark, predicates from the WHERE clause may already be evaluated by Spark, reducing the number of tuples sent. However, currently this works only under the following circumstances:
• It is implemented for all Actian Ingres column data types except “time with time zone” and “timestamp with timezone.”
• Supported predicates are: <, <=, =, >=, and >. Using “like” is currently unsupported. Only predicates that can be translated into a column value range are supported.
• For logical connections of simple predicates, only AND is supported. If the whole complex predicate contains a single OR, nothing is pushed down to Spark. In this case, Spark transfers all tuples to Actian Ingres and the filtering is done solely on the Actian Ingres side.
Predicate pushdown is considered only from Actian Ingres to Spark. The predicate evaluation on the Spark side is determined by the Spark framework. How far Spark and its internal optimizer push down predicates (i.e. how close to the source) depends on the specific file format and the Spark version. For example, in Spark 2, Parquet files may already be filtered during parsing, but this is not possible for CSV files. Filtering during CSV file parsing is supported in Spark 3.