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, orcavro, 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.

header

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.


Was this article helpful?
0 out of 0 found this helpful