Fast, Robust and Large-Scale Tabular Import/Export

Piotr Wendykier, Wolfram Research

(RowDefault[, (Spacer1), , (Spacer1), ])  #WolframTechConf

Outline

Columnar data structure (Tabular)

Significant updates in CSV and TSV formats

New formats: Parquet, ArrowDataset, ArrowIPC, ORC

Updates in spreadsheet and statistical formats

Get data from “anywhere”: DataConnectionObject

Columnar Data Structure

Also known as data frame, table and structured data

Typically each column represents a variable and each row represents a measurement

Each column is usually of a homogeneous type such as number, category, string or date

Data entries can be missing

Tabular

Columnar data structure in the Wolfram Language is called Tabular

Other talks (earlier today):

Transforming Tabular Data, Roger Germundsson and Rob Knapp

Tabular Visualization, Nirmal Malapaka

Tabular Displays, Jason Abernathy

Create Tabular object by importing data from CSV file

tabular=Import["ExampleData/TreesOwnedByTheCityOfChampaign.csv","Tabular"]

Type information

tabular["ColumnTypes"]

Select columns

tabular[All,"Common Name"|"Number of Trunks"]

Select rows

tabular[1;;3]

CSV and TSV: Improved Column Type Inference

In WL 14.2 “Tabular” Import element automatically parses ”start_date” and “end_date” columns as Date type

tournaments=FileNameJoin[{NotebookDirectory[], "Files","tournaments.csv"}];

tabular=Import[tournaments,"Tabular"];

tabular[All,"start_date"|"end_date"]["ColumnTypes"]

In WL 14.1 “Dataset” Import element parses these columns as strings

dataset=Import[tournaments,"Dataset","HeaderLines"->1];

DeleteDuplicates[Head/@dataset[[All,"start_date"]]]

DeleteDuplicates[Head/@dataset[[All,"end_date"]]]

CSV and TSV: Performance

Performance of Import and Export

Test dataset: NYC Yellow Taxi Trip Data 2015

File size: 1.84 GB

Dimensions: 12748986 rows and 19 columns

Column types: {"Integer64","Date"::["Integer64","Second"->"Second",Automatic,None],"Date"::["Integer64","Second"->"Second",Automatic,None],"Integer64","Real64","Real64","Real64","Integer64","String","Real64","Real64","Integer64","Real64","Real64","Real64","Real64","Real64","Real64","Real64"}

“Data” Import element was used in WL 14.1 and “Tabular” Import element was used in WL 14.2

Grid[{{"","Import time (s)","Export time (s)"},{"WL 14.2",6.7,33.7},{"WL 14.1",206.4, 1211.6}},Alignment->Left,Spacings->{2,1},Frame->All,ItemStyle->"Text",Background->{{LightGray,None},{LightGray,None}}]

CSV and TSV: Header Detection

Headers are recognized automatically in WL 14.2

Import["ExampleData/financialtimeseries.csv",{"Tabular",1;;3}]

%["ColumnKeys"]

Import["ExampleData/TreesOwnedByTheCityOfChampaign.csv",{"Tabular",1;;3}]

%["ColumnKeys"]

Incorrect header detection can be adjusted using “HeaderLines” and “SkipLines” options

Import[FileNameJoin[{NotebookDirectory[], "Files","country-names-and-codes.csv"}],{"Tabular",1;;4}]

Import[FileNameJoin[{NotebookDirectory[], "Files","country-names-and-codes.csv"}],{"Tabular",1;;4}, "HeaderLines"->1]

CSV and TSV: Missing Elements Detection

Missing elements are recognized automatically

fileWithMissingValues=FileNameJoin[{NotebookDirectory[], "Files","missing.csv"}];

tabular=Import[fileWithMissingValues,"Tabular"]

Map[#["MissingCount"]&,tabular["Columns"]]

ImportExport`$DefaultMissingValues

MissingValuePattern option takes the list of strings, StringExpression or RegularExpression

tabular2=Import[fileWithMissingValues,"Tabular", MissingValuePattern->("-"~~NumberString )|""]

Map[#["MissingCount"] &, tabular2["Columns"]]

CSV and TSV: Backward-incompatible Changes

For most of the files backward-incompatible changes should not be visible

Default value of “CurrencyTokes” is equal None in WL 14.2

ImportString["$1.99,£5.99\n$1.99,£5.99","CSV"]

ImportString["$1.99,£5.99\n$1.99,£5.99","CSV", "CurrencyTokens"->{{"$","£"},{"¢","p"}}]

Export converts real numbers with 0 fractional part to integers

ExportString[{{"",1,1.},{"",2,2.}},"CSV"]

Export adds quoting characters around empty strings

ExportString[{{"",1,1.},{"",2,2.}},"CSV"]

ExportString[{{"",1,1.},{"",2,2.}},"CSV", "IncludeQuotingCharacter"->None]

Date columns are exported using DateString[..., “ISODateTimeMillisecondFull”]

ExportString[,"CSV"]

Spreadsheet and Statistical Formats

Spreadsheet formats: XLS, XLSX, ODS, SXC, DIF

Statistical formats: DTA, SAS7BDAT, XPORT, SAV, POR, RData, RDS

“Tabular” Import element

Import["ExampleData/elements.xls","Tabular"]

“TabularAssociation” Import element (only in spreadsheet formats and RData)

Import["ExampleData/wind.xls","TabularAssociation"]

Automatic header detection

Import["ExampleData/cities.xlsx",{"Tabular",1}]

Import["ExampleData/cities.xlsx",{"Tabular",1}, "HeaderLines"->0]

Possible Import issues

Import["ExampleData/wind.xls",{"Tabular",1}]["ColumnTypes"]

Import["ExampleData/wind.xls",{"Tabular",1,1;;-3}]["ColumnTypes"]

Export “Tabular” to XLSX

tabular=Import["ExampleData/elements.xls","Tabular"];

Import[Export["out.xlsx",tabular],"Tabular"]

Parquet, ArrowIPC and ORC

Default Import element: “Tabular”

Import["ExampleData/USStates.arrow"]

All column types of Tabular object are supported by ArrowIPC and Parquet formats

Some column types of Tabular object are not supported by ORC format: Time, Categorical, ListTuple, ...

Export serializes columns of type “InertExpression” using BinarySerialize

Import deserializes columns of type “InertExpression” using BinaryDeserialize

Compression methods in Export to ArrowIPC

Grid[{{"","File size","Export time (s)"},{"Uncompressed",(Quantity[1.9, "GB", gigabytes, "Gigabytes"]), 1.3},{"LZ4Frame",(Quantity[0.56, "GB", gigabytes, "Gigabytes"]), 1.36},{"ZSTD",(Quantity[326.4, "MB", megabytes, "Megabytes"]), 1.65}},Alignment->Left,Spacings->{2,1},Frame->All,ItemStyle->"Text",Background->{{LightGray,None},{LightGray,None}}]

Compression methods in Export to Parquet

Grid[{{"","File size","Export time (s)"},{"Uncompressed",(Quantity[398.6, "MB", megabytes, "Megabytes"]), 5.58},{"Snappy",(Quantity[307, "MB", megabytes, "Megabytes"]), 6.24},{"GZIP",(Quantity[243.4, "MB", megabytes, "Megabytes"]), 26.13},{"Brotli",(Quantity[229.8, "MB", megabytes, "Megabytes"]), 34.96},{"ZSTD",(Quantity[254.5, "MB", megabytes, "Megabytes"]), 6.45},{"LZ4",(Quantity[308.5, "MB", megabytes, "Megabytes"]), 6.17},{"LZ4Hadoop",(Quantity[308.5, "MB", megabytes, "Megabytes"]), 5.99}},Alignment->Left,Spacings->{2,1},Frame->All,ItemStyle->"Text",Background->{{LightGray,None},{LightGray,None}}]

Compression methods in Export to ORC

Grid[{{"","File size","Export time (s)"},{"Uncompressed",(Quantity[1.9, "GB", gigabytes, "Gigabytes"]), 5.58},{"Snappy",(Quantity[307, "MB", megabytes, "Megabytes"]), 6.24},{"GZIP",(Quantity[243.4, "MB", megabytes, "Megabytes"]), 26.13},{"ZSTD",(Quantity[254.5, "MB", megabytes, "Megabytes"]), 6.45},{"LZ4",(Quantity[308.5, "MB", megabytes, "Megabytes"]), 6.17}},Alignment->Left,Spacings->{2,1},Frame->All,ItemStyle->"Text",Background->{{LightGray,None},{LightGray,None}}]

Parquet, ArrowIPC and ORC: Performance Comparison

Performance comparison with CSV in WL 14.2 (NYC Yellow Taxi Trip Data 2015)

Grid[{{"","Import time (s)","Export time (s)"},{"CSV",6.7,33.7},{"ArrowIPC",1.3, 1.5},{"Parquet",1.1, 5.9},{"ORC",2.0, 6.8}},Alignment->Left,Spacings->{2,1},Frame->All,ItemStyle->"Text",Background->{{LightGray,None},{LightGray,None}}]

ArrowDataset

Efficient storage format for multi-file datasets

It is a directory with files in one of the following formats: ArrowIPC, Parquet, ORC, CSV or TSV

Usually it is a partitioned dataset - one or more columns is used for partitioning and subdirectory names hold information about which subset of the data is stored in that directory

Export example:

tabular=Import["ExampleData/TreesOwnedByTheCityOfChampaign.csv","Tabular"];

Dimensions[tabular]

tabular["ColumnKeys"]

outDir=FileNameJoin[{$TemporaryDirectory, "tabular_dataset"}];

Export[outDir,tabular,"ArrowDataset","SplitColumns"->"Number of Trunks",OverwriteTarget->True];

Import example:

FileSystemMap[FileSize,outDir, Infinity]

tabular2=Import[FileNameJoin[{outDir, "Number of Trunks=0","part0.parquet"}]]

Dimensions[tabular2]

tabular3=Import[outDir, "ArrowDataset", "Partitioning"->"Hive"]

Dimensions[tabular3]

DataConnectionObject

Overview:

An object to a remote resource such as Amazon S3, Azure Blob Storage, Dropbox, etc

Create a notebook with a remote data source providing Authentication, or a dialog to give your own credentials so colleagues can work on the same data

Control over refreshing the data or using locally cached results

The list of our supported data sources will grow faster than ever

Seamlessly integrated with Import

Examples:

Create a DataConnectionObject:

DataConnectionObject[
<|"ConnectionName"->"AzureBlobStorage","Account"->"dataconnection","Container"->"samples","Location"->"sample.parquet"|>]

Get the FileFormat of the data:

FileFormat[DataConnectionObject[]]

Import some tabular data:

Import@DataConnectionObject[]

One line analytics:

AggregateRows[Import@DataConnectionObject[],"average"->(Mean[#numbers]&),"expr_types"->(Head[#expressions]&)]