Skip to main content

Create Table From File

Creates a Magpie table that references a file on an HDFS file system data source.

Syntax​

create [temp | temporary] table { <table spec> | <table name> } [in schema <schema reference>]
  from data source <data source reference> file "<file name>"
  [at path "<path>"]
  [with infer schema]
  [with format <file format>]
  [with quote char "<quote character>"]
  [with escape char "<escape character>"]
  [with null value "<null value>"]
  [with delimiter "<delimiter>"]
  [with encoding "<encoding>"]
  [with date format "<date format>"]
  [with timestamp format "<timestamp format>"]
  [with multiline]
  [with header]
  [with ignore leading white space]
  [with ignore trailing white space]
  [with ignore extension]
  [with merge schema]

Parameters​

table spec​

JSON — A specification for saving the table. Note that the persistence mapping and fields are not used when creating a table from file.

table name​

String — The name of the table to save the data as.

schema reference​

String — The name of the schema to create the table in. Defaults to the current schema.

data source reference​

String — The name of the data source that the file is located in.

file name​

String — The name of the file on the file system.

path​

String — The parent path of the file on the file system. Defaults to an empty string.

infer schema​

None. If present, this option causes schema inference to occur, attempting to identify the data types of each field in the file. Only used for csv files.

file format​

String — The format of the source file. Supported formats: text, parquet, csv, json, orc, avro, or delta. Default is parquet.

quote character​

String — The character optionally used to enclose fields within the file. Only used for csv files. Default is ".

escape character​

String — The character optionally used to escape quotations within a quoted field. Only used for csv files. Default is ".

null value​

String — Fields of this value will be converted to null. Only used for csv files. Default is that empty fields are converted to null.

delimiter​

String — The character used to separate fields within the file. Only used for csv files. Default is ,.

encoding​

String — The encoding of the file. Only used for csv and json files. Default is UTF-8 for csv and newline-delimited json and auto-detected for multi-line json.

date format​

String — The Java date format used to identify fields as dates within the file. Only used for csv and json files. Default is yyyy-MM-dd.

timestamp format​

String — The Java datetime format used to identify fields as timestamps within the file. Only used for csv and json files. Default is yyyy-MM-dd'T'HH:mm:ss.SSSXXX for json and yyyy-MM-dd HH:mm:ss for csv.

multiline​

None. If present, this option enables parsing multiple lines as one record. Only used for csv and json files.

None. If present, the first line of the file will be used as field names for the resulting table. Only used for csv files.

ignore leading white space​

None. If present, leading white space will be trimmed from each field. Only used for csv files.

ignore trailing white space​

None. If present, trailing white space will be trimmed from each field. Only used for csv files.

ignore extension​

None. If present, read all files at the specified path and file name regardless of whether they end in .avro. By default, only .avro files are read and others are ignored. Only used for avro files.

merge schema​

None. If present, union the fields present in all of the parquet files at the given path and file name to determine the schema of the new table. By default, only the fields present in the first file are used. Only used for parquet or delta files.