Convert HIVE table to AVRO format and export as AVRO file

Step 1:

Create an new table using AVRO SERDE based off the original table in HIVE. You can do it in HUE data browser:

CREATE TABLE avro_test_table
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'
TBLPROPERTIES (
    'avro.schema.literal'='{
      "namespace": "testnamespace.avro",
      "name": "testavro",
      "type": "record",
      "fields": [ {"name":"strt_tstmp","type":"string"},{"name":"end_tstmp","type":"string"},{"name":"stts_cd","type":"int"}]
    }');

This will create a new table in AVRO compatible format in HIVE.

Step 2: Load data from the original table

INSERT OVERWRITE TABLE avro_test_table SELECT *  FROM tablename;

Step 3: Go to HUE file browser and see if the file is created. It might be created with file names as some zeroes without any extension. You should look for this file in one of the sub-folders of your application directory for ex: /data/apps/appname/appname_db/avro_test_table/xxx

Step 4:
Run the following command from command line in Hadoop

hadoop fs -cat /data/apps/appname/appname_db/avro_test_table/xxx > testdata.avro

Step 5: Look for this avro file in your home directory in edge node.


Comments

Post a Comment

Popular posts from this blog

Setting property 'keystoreFile' did not find a matching property. No Certificate file specified or invalid file format

I encountered this weird exception on X server even after configuring the Tomcat  configuration file properly same as i did for other server  Y where it is working fine. Solution is mentioned after the exception log: May 24, 2011 5:23:45 PM org.apache.catalina.startup.SetAllPropertiesRule begin WARNING: [SetAllPropertiesRule]{Server/Service/Connector} Setting property 'keystoreFile' to 'C:\Program Files\Java\jdk1.6.0_25\keystore\.keystore' did not find a matching property. May 24, 2011 5:23:45 PM org.apache.catalina.startup.SetAllPropertiesRule begin WARNING: [SetAllPropertiesRule]{Server/Service/Connector} Setting property 'keystorePass' to 'changeit' did not find a matching property. May 24, 2011 5:23:45 PM org.apache.catalina.startup.SetAllPropertiesRule begin WARNING: [SetAllPropertiesRule]{Server/Service/Connector} Setting property 'clientAuth' to 'false' did not find a matching property. May 24, 2011 5:23:46 PM org.apache.coyote.h
Read more

Load data from CSV into HIVE table using HUE browser

It may be little tricky to load the data from a CSV file into a HIVE table. Here is a quick command that can be triggered from HUE editor. Steps: 1. Upload your CSV file that contains column data only (no headers) into use case directory or application directory in HDFS 2. Run the following command in the HIVE data broswer LOAD DATA  INPATH "/data/applications/appname/table_test_data/testdata.csv" OVERWRITE INTO TABLE testschema.tablename; 3. This will overwrite all the contents in the table with the data from csv file. so existing data in the table will be lost Make sure the table is already created in the HIVE. You can create the table as follows: CREATE TABLE   tablename( ·   strt_tstmp string , end_tstmp string , stts_cd int , ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ·   STORED AS TEXTFILE  
Read more

Gitlab change project visibility from private to internal

Apparently there is no clicable option in the GITlab portal to change the project visibility from private to internal or to public once that is set at the time of creation. In order to do this, you will have to add "/edit" string to the end of your repo URL and it will show the setting page. The "Permissions" section can be expanded to change the visibility and click "Save Changes" to get your changes reflected. Hope this helps.
Read more