How to ingest data from Azure DataMarket into Hadoop
For those of you who haven't encountered it yet, Azure DataMarket is quite an exciting platform by Microsoft which provides standardized access to plenty of interesting datasets. As of now, there are over 200 datasets from broad range of topics, including weather, demographics, automotive, agriculture, real-estate, etc. There might be a golden nugget for your bussiness hidden in there, so I would definitely recommend to go and check the platform out.
Recently, we have discovered such a golden nugget ourselves: details about every car sold in Netherlands in the past 15 years. A pretty exciting dataset, considering that the company I work for operates in the Dutch market of electric vehicles. What is more, the data is free, updated daily and comes shipped with a REST API interface. What more can we wish for?
To maximally leverage potential of the dataset, we ingest it into Hive, which allows us to:
- run fast ad-hoc exploratory SQL queries with Impala,
- explore the dataset with BI tools,
- enrich the data model by combining car details with other data and
- expand our dashboards with insights about the car market.
Avro-backed external Hive tables FTW!
- Hive automatically infers table schema from the Avro schema. There is no need to explicitly enumerate columns with their types, which is very useful if you deal with tables of many columns.
- Changes in table schema are not an issue. You just recreate the table with the new Avro schema and the Avro schema evolution will automagically take care of properly reading the data stored with the old schema.
- Data is easily processed by other tools like Apache Spark or MapReduce. The data resides in a directory of your choice (rather than in a Hive directory) and the Avro format has first-class support in Hadoop tooling.
- New data is easily added to the table. Just add a new Avro file to the corresponding HDFS directory and Hive will automatically pick it up. With Impala, call
REFRESH <table-name>;to notify Impala about arrival of new data.
- Hive data partitioning is well supported so you can optimize for query performance.
- Dropping external Hive table does not delete data from HDFS. Small thing, but very handy.
Ingestion: From OData XML to an Avro Hive table
Here are detailed steps how we get that data from DataMarket into Hive.
Step 1: Download the dataset as XML files, in a standardized OData format
DataMarket publishes datasets as XML. Each XML follows the same OData schema (checkout the example XML with cars data). DataMarket provides this data via a REST interface, which supports various query parameters. Using the parameters, you control the subset of data you want to download.
Since the car dataset that we are interested in is about 15 GiB large, filtering proved very useful for our purposes. With the
$inlinecount parameters and a bit of
curl and Bash, it is straightforward to download the whole dataset into files
cars_2015.xml, where the number indicates the year where a car was registered in Netherlands.
Step 2: Convert the dataset into Avro, using
To convert an XML file in OData schema to Avro, we created a Python command-line tool called odata2avro which does all the heavy lifting for you.
Just install the tool with
pip install odata2avro and use it as follows:
$ odata2avro cars_2013.xml cars.avsc cars_2013.avro
This command reads an XML file
cars_2013.xml and creates two files:
cars.avsc- an Avro schema, in json, describing the dataset,
cars_2013.avro- a binary Avro file containing the dataset from the XML.
Step 3: Upload the Avro schema and Avro files to HDFS
In this case, let's create and upload the data to
/datamarket HDFS directory:
$ hdfs dfs -mkdir -p /datamarket/cars $ hdfs dfs -put cars_20*.xml /datamarket/cars $ hdfs dfs -put cars.avsc /datamarket
Step 4: Create an external Avro-backed Hive table
To create an external Hive table with a schema according to
/datamarket/cars.avsc and the data located in
/datamarket/cars, use the following Hive command with the AvroSerDe:
CREATE EXTERNAL TABLE cars ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION '/datamarket/cars' TBLPROPERTIES ('avro.schema.url'='hdfs:///datamarket/cars.avsc');
Step 5: Query & profit!
Congrats! As of now, the data is accessible in both Hive and Impala:
$ impala-shell --query 'select * from cars;'
Bonus step: Keeping data updated
The car dataset is append-only, so for us it's pretty straightforward to keep the data in Hive updated. We run a daily job which:
- downloads XML data from DataMarket for the current year 2015,
- converts the XML data to an Avro file called
- uploads the Avro file to HDFS and replaces the file uploaded on the previous day and
REFRESH cars;so Impala registers the new data.
If you need help with building the tech products get in touch.