{"id":3144,"date":"2018-09-10T14:56:33","date_gmt":"2018-09-10T20:56:33","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/?p=3144"},"modified":"2021-11-21T12:38:40","modified_gmt":"2021-11-21T18:38:40","slug":"aborted-adventures-with-amazon-athena-and-us-pto-data","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/3144","title":{"rendered":"Aborted Adventures with Amazon Athena and US PTO data"},"content":{"rendered":"

\"Goddesses\"I was playing around recently with some data (from the US Patent and Trademark Office<\/a>), trying to import it into S3 and then to Athena. Athena<\/a> is a serverless big data query engine with schema on read semantics. The data was not available on the AWS public dataset repo<\/a>. Things didn’t go as well as planned. Here’s how I wanted them to go:<\/p>\n

    \n
  1. download some data<\/li>\n
  2. transform it into CSV (because Athena doesn’t support currently XML<\/a> and I didn’t want to go full EMR, even though hive supports XML<\/a>)<\/li>\n
  3. upload it to s3 bucket<\/li>\n
  4. create a table based on the data<\/li>\n
  5. run some interesting queries using Athena<\/li>\n
  6. possibly pull some of the data in Amazon Machine Learning to do some predictions<\/li>\n
  7. possibly put some of the data in an s3 bucket as JSON and use datatables<\/a> to create a nice user interface<\/li>\n<\/ol>\n

    Like pretty much every development project I’ve ever been part of, there were surprises. What was different is that I had a fixed amount of time since this was an exploratory project, I set a timebox. I didn’t complete much of what I wanted to get done, but wanted to document what I did.<\/p>\n

    I was able to get through step 5 with a small portion of data (13k rows). I ended up working a lot on windows because I didn’t want to boot up a vagrant box. I spent a lot of time re-learning XSLT in order to pull the data I wanted out of the XML. I used a tool called xmlstarlet<\/a> for this, which worked pretty well with the small dataset. Here’s the command I ran to pull out some of the attributes of the XML dataset (you can see that I also learned about batch file arguments):<\/p>\n

    xml sel -T -t -m \/\/case-file -v \"concat(serial-number,',',registration-number,',',case-file-header\/registration-date,'\\n<\/code><\/p>\n

    ,',case-file-header\/status-code,',',case-file-header\/attorney-name)\" -n %filename% > %outfile%<\/code><\/p>\n

    And here’s the Athena schema I created:<\/p>\n


    \nCREATE EXTERNAL TABLE trademark_csv (
    \nserialnumber STRING,
    \nregistrationnumber STRING,
    \nregistrationdate STRING,
    \nstatuscode INT,
    \nattorneyname STRING
    \n)
    \nROW FORMAT DELIMITED
    \nFIELDS TERMINATED BY ','
    \nESCAPED BY '\\\\'
    \nLINES TERMINATED BY '\\n'
    \nLOCATION 's3:\/\/aml-mooreds\/athena\/trademark\/';
    \n<\/code><\/p>\n

    After I had done the quick prototype, I foolishly moved on to downloading the full dataset. This caused some issues with disk storage and ended up taking a long time (the full dataset was ~300 files from 500M to 2GB in size, each containing about 150k records). I learned that I should have pulled down one large file and worked it through my process rather than making automating each step as I went. For one, xmlstarlet hasn’t been updated for years, and I couldn’t find a linux package. When tried to compile it, it was looking for libxml, which was installed on my ec2 instance already. I didn’t bother to head further down this path. But I ran into a different issue. When I ran xmlstarlet against a 500MB uncompressed XML file, it completed. But any of the larger files caused it to give an ‘out of memory’ error. I saw one reference in the bugtracker<\/a>, but it didn’t seem to apply.<\/p>\n

    So, back to the drawing board. Luckily, many languages have support for event based parsing of XML. I was hoping to find a command line tool that could run XSLT in order to reuse some of my logic, but it doesn’t appear to exist (found this interesting discussion<\/a> and this one<\/a>). python seemed like it might work well<\/a>.<\/p>\n

    Then I ran out of time. Oh well, maybe some other time. It is fun to think about how I can automate all of this. I was definitely seeing where lambda functions and some other AWS features could have fit in nicely. I also think that using RDS might have made more sense than Athena, given the rate of update and the amount of data.<\/p>\n

    Lessons learned:<\/p>\n