Hive storage, parsing, processing JSON data

Hive handles JSON data in a way that has two directions in general.

1, the JSON as a string into the Hive table, and then by using the UDF function to resolve the data that has been imported into hive, such as using the lateral VIEW json_tuple method, get the required column name.

2. Before importing the JSON into fields, the data imported into the Hive table has been parsed. This will require the use of third-party serde.

Test data for Sina Weibo test public data

This data is stored in JSON format,
ID represents the ID of the current user Weibo,
IDS represents current Weibo users ' attention to the list of other Weibo users ' IDs,
Total_number is concerned about the total number of Weibo users.

{"id": 1701439105, "IDs": [2154137571,3889177061,1496915057,......, 1663973284], "Total_number": 493}

The first type:

Import data

CREATE TABLE IF  not EXISTS tmp_json_test (           as  textfile; Load ' /opt/datas/weibotest.json '  into table tmp_json_test;

Parsing data:

SelectGet_json_object (T.json,'$.id'), Get_json_object (T.json,'$.total_number') fromTmp_json_test t; SelectT2.*  fromtmp_json_test T1 LateralViewJson_tuple (T1.json,'ID','Total_number') T2 asC1, C2; Method one uses the function Get_json_object, the method two uses the function Json_tuple

The second type:

The second approach is more flexible and more generic than the first. It is important that each line must be a full JSON, and that a JSON cannot span multiple lines.

1. Download jar
Download the jar before use:

http://www.congiu.net/hive-json-serde/
If you want to use Jsonserde in hive, you need to add the jar to the hive classpath:

Add Jar Json-serde-1.3.7-jar-with-dependencies.jar;

Import data

CREATE TABLE Tmp_json_array (ID string,ids array<string>int'  org.openx.data.jsonserde.JsonSerDe' as textfile; LOAD ' /opt/datas/weibotest.json '  into TABLE  Tmp_json_array;

You can use it when you pour it.

Select *  from where array_contains (IDs,'2813165271'or array_contains (IDs,'  1419789200');

It is important to note that when your data contains rows that do not conform to the JSON specification, running the query will report an exception

Test can increase configuration to skip error data

ALTER TABLE SET = "true");

There is no error in running the query, but the bad data record becomes null.

The last thing to be reminded is that when your JSON data contains hive keywords, the imported data is problematic, and Serde can use the Serde property to map hive columns to different names of properties

If IDs is the Hive keyword, the more altered table statements are as follows:

CREATE TABLE Tmp_json_array (ID string,ids_alias array<string>int  ' org.openx.data.jsonserde.JsonSerDe '  with Serdeproperties ("mapping.ids_alias"= as Textfile;

Hive storage, parsing, processing JSON data