The last blog entry showed you how to use a CSV file together with Apache Drill.
In this blog entry we will convert the CSV file into parquet files. This gives you the advantage of faster query processing for certain queries as parquet organizes the data in columns and not in rows. We will also partition the data. This means that a parquet file is created for each distinct value of the partitioned column(s). In our example we partition per country, so we will have a parquet file per country.
The conversion is actually very easy. Once you have defined a query that works and includes the conversion of the CSV columns to their proper types, all that needs to be added is the CTAS statement - "Create table as" as can be seen below. When using the web interface you will also need to specify the storage -`dfs.local` in the example. Partitioning is defined using the "partition by" statement with the column or columns in round brackets.
Notice that I have also removed the "limit 20" statement from the query I used previously because otherwise not all records from the CSV are used.
What's left now is to simply run the query. I have selected to name the table "airports". This in turn will create a folder named "airports" in the filesystem at the location which is defined by `dfs.local` and within this folder one file for each country.
Once the query completes, you are ready to run queries on the parquet files. For example finding all airports with a specific country code like in the screenshot below (country 'DE' = Germany).
Notice we can use the names of the columns (no aliases) because we specified them when we created the parquet files and we use "airports" as the name of the table.
RSS Feed