Table is
For Excel, XML, or Parquet files, you can specify the table you are loading data from in the table format specifier.
Syntax:
Table is table name
Arguments:
Arguments
| table name |
Specifies the name of the table. The value depends on the table format:
-
Excel: The sheet name.
-
XML: The path that specifies the part of the XML to be loaded.
-
Parquet: The path that specifies the table, with the format <node>.<node>.<node>.
Use Table
is when specifying a table within a nested structure.
For example, you have Parquet data in the following schema:
Schema:
Field(name: "Name", datatype: String),
Field(name: "Age", datatype: Float),
Field(name: "Phone", datatype: List(
Field(name: "Item", datatype: Struct[
Field(name: "Number", datatype: String)
You could load Phone and its nested fields as a table with the argument Table is [Schema:Phone.Item]. This will generate the key field %Key_Phone with the table.
|
Example: Excel
LOAD
"Item Number",
"Product Group",
"Product Line",
"Product Sub Group",
"Product Type"
FROM [lib://DataFiles/Item master.xlsx]
(ooxml, embedded labels, table is [Item master]);
Example: XML
LOAD
city%Table,
%Key_row_7FAC1F878EC01ECB
FROM [lib://DataFiles/cities.xml]
(XmlSimple, table is [root/row/country/city]);
Example: Parquet
The file company.parquet contains the following schema:
company (String)
contact (String)
company:salesrep (List)
salesrep (Group)
salesrep (String)
company:headquarter (List)
headquarter (Group)
country (String)
city (String)
city:region (List)
region (Group)
region (String)
The following would load the contents from the file into tables. The first load statement loads the root group. The second load statement loads the contents of the salesrep group as a table. The third loads the headquarter group as a table. The fourth loads the region group in as a table.
LOAD * FROM […] (parquet);
LOAD * FROM […] (parquet, table is [company:salesrep.salesrep]);
LOAD * FROM […] (parquet, table is [company:headquarter.headquarter]
LOAD * FROM […] (parquet, table is [company:headquarter.headquarter.city:region.region]
Limitations:
The Table
is specifier is only relevant for Excel, XML, or Parquet
files.
