{"action":"created","issue":{"url":"https://api.github.com/repos/dmwm/WMArchive/issues/80","repository_url":"https://api.github.com/repos/dmwm/WMArchive","labels_url":"https://api.github.com/repos/dmwm/WMArchive/issues/80/labels{/name}","comments_url":"https://api.github.com/repos/dmwm/WMArchive/issues/80/comments","events_url":"https://api.github.com/repos/dmwm/WMArchive/issues/80/events","html_url":"https://github.com/dmwm/WMArchive/issues/80","id":128866670,"number":80,"title":"Resolve hive issue with FWJR","user":{"login":"vkuznet","id":125138,"avatar_url":"https://avatars.githubusercontent.com/u/125138?v=3","gravatar_id":"","url":"https://api.github.com/users/vkuznet","html_url":"https://github.com/vkuznet","followers_url":"https://api.github.com/users/vkuznet/followers","following_url":"https://api.github.com/users/vkuznet/following{/other_user}","gists_url":"https://api.github.com/users/vkuznet/gists{/gist_id}","starred_url":"https://api.github.com/users/vkuznet/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vkuznet/subscriptions","organizations_url":"https://api.github.com/users/vkuznet/orgs","repos_url":"https://api.github.com/users/vkuznet/repos","events_url":"https://api.github.com/users/vkuznet/events{/privacy}","received_events_url":"https://api.github.com/users/vkuznet/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2016-01-26T16:12:54Z","updated_at":"2016-02-03T01:08:09Z","closed_at":null,"body":"We would like to use Hive tool to search over our data. The data will resides somewhere on hadoop cluster along with some schema which describe the data. Then we want to create an external hive table which will allow us to search over our data via Hive queries.\r\n\r\nTo do this task few steps should be performed\r\n* create your own JSON file or take one from https://github.com/dmwm/WMArchive/tree/master/test/data (for reference I'll refer to it as file.json)\r\n* create avro schema file for your JSON file (for references I'll refer to it as file.avsc)\r\n* create avro files from your JSON file and avro schema file (for references I'll refer to it as file1,2,3.avro)\r\n\r\nThe file with avsc extension is a avro schema file. To generate avsc and avro files someone can ```json2avsc``` and ```json2avro``` scripts from https://github.com/dmwm/WMArchive\r\n\r\nHere are steps to setup necessary software;\r\n\r\n```\r\ncd some_dir\r\ngit clone git@github.com:dmwm/WMArchive.git\r\nexport PYTHONPATH=$PWD/WMArchive/src/python\r\nexport PATH=$PATH:$PWD/WMArchive/bin\r\n```\r\n\r\nand this will put into your PATH and PYTHONPATH all necessary stuff. Then you can run\r\n\r\n```\r\n# to generate file.avsc out of file.json\r\njson2avsc --fin=file.json --fout=file.avsc\r\n\r\n# to generate file.avro out of file.json/file.avsc\r\njson2avro --fin=file.json --schema=file.avsc --fout=file.avro\r\n```\r\n\r\nOnce you generate avsc and avro files you can put them into HDFS\r\n\r\n```\r\n# ssh to analytix\r\n# create dir for hive tests\r\nshell# hadoop fs -mkdir /user/valya/test/hive\r\n# create dir for data within hive dir\r\nshell# hadoop fs -mkdir /user/valya/test/hive/data\r\n\r\n# copy avsc to hive dir\r\nshell# hadoop fs -cp file.avsc /user/valya/test/hive\r\n\r\n# copy avro to hive data dir, I replicate data multiple times\r\nshell# hadoop fs -cp file.avro /usre/valya/test/data/file1.avro\r\nshell# hadoop fs -cp file.avro /usre/valya/test/data/file2.avro\r\n```\r\n\r\nAt this step you'll have avsc and avro files on HDFS. Now we can create hive table.\r\n\r\nThe hive table can be created as following:\r\n```\r\n# start hive on analytix cluster\r\nanalytix# hive\r\n\r\n# within hive shell invoke the following command \r\n# please change paths accordingly to your data location\r\n# in this case I used /user/valya/test/hive directory on HDFS\r\n# and arch.db is a file name for my hive DB\r\n\r\ncreate database cms_wm_archive location '/user/valya/test/hive/arch.db';\r\n\r\n# please change hdfs path to path where your schema file will reside\r\n# in this case it is under\r\n# /user/valya/test/hive/fwjr_processing.avsc\r\n\r\ncreate external table wmarchive\r\nROW FORMAT SERDE \"org.apache.hadoop.hive.serde2.avro.AvroSerDe\"\r\nSTORED AS INPUTFORMAT \"org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat\"\r\nOUTPUTFORMAT \"org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat\"\r\nlocation \"/user/valya/test/hive/data\"\r\nTBLPROPERTIES(\"avro.schema.literal\"=\"hdfs://p01001532965510.cern.ch:9000/user/valya/test/hive/file.avsc\");\r\n```\r\n\r\nOnce table is created we can try to query our data via Hive queries, see \r\nhttp://thornydev.blogspot.com/2013/07/querying-json-records-via-hive.html\r\nhttps://docs.treasuredata.com/articles/hive\r\n"},"comment":{"url":"https://api.github.com/repos/dmwm/WMArchive/issues/comments/178932212","html_url":"https://github.com/dmwm/WMArchive/issues/80#issuecomment-178932212","issue_url":"https://api.github.com/repos/dmwm/WMArchive/issues/80","id":178932212,"user":{"login":"vkuznet","id":125138,"avatar_url":"https://avatars.githubusercontent.com/u/125138?v=3","gravatar_id":"","url":"https://api.github.com/users/vkuznet","html_url":"https://github.com/vkuznet","followers_url":"https://api.github.com/users/vkuznet/followers","following_url":"https://api.github.com/users/vkuznet/following{/other_user}","gists_url":"https://api.github.com/users/vkuznet/gists{/gist_id}","starred_url":"https://api.github.com/users/vkuznet/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vkuznet/subscriptions","organizations_url":"https://api.github.com/users/vkuznet/orgs","repos_url":"https://api.github.com/users/vkuznet/repos","events_url":"https://api.github.com/users/vkuznet/events{/privacy}","received_events_url":"https://api.github.com/users/vkuznet/received_events","type":"User","site_admin":false},"created_at":"2016-02-03T01:08:08Z","updated_at":"2016-02-03T01:08:08Z","body":"I can also load avro schema from HDFS, i.e.\r\n```\r\ncreate external table wmarchive ROW FORMAT SERDE\r\n \"org.apache.hadoop.hive.serde2.avro.AvroSerDe\"\r\nSTORED AS INPUTFORMAT\r\n \"org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat\"\r\nOUTPUTFORMAT \r\n \"org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat\"\r\nLOCATION \"/user/valya/test/hive/data/\" TBLPROPERTIES(\"avro.schema.url\"=\"hdfs:///user/valya/test/hive/fwjr.avsc\");\r\n```"}}