Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

HIVE DDL for complex type CSV containing Json obejct

Highlighted

HIVE DDL for complex type CSV containing Json obejct

New Contributor

Hi folks,

I am trying to load following data into hive tables. failing to load json value [host_attributes column which is json object] into the table:

host_id,host_name,cpu_count,ph_cpu_count, cpu_info,discovery_status,host_attributes,ipv4,ipv6,public_host_name,last_registration_time,os_arch,os_info,os_type,rack_info,total_mem

1,abc1103.xyz.com,8,8,,,{"interfaces":"ens192,lo","os_family":"redhat","kernel":"Linux","timezone":"GMT","kernel_release":"3.10.0-862.11.6.el7.x86_64","os_release_version":"7.5","physicalprocessors_count":"8","hardware_isa":"x86_64","kernel_majorversion":"3.10","kernel_version":"3.10.0","netmask":"255.255.255.0","mac_address":"xx:xx:xa:aa","swap_free":"8.00 GB","swap_size":"8.00 GB","selinux_enabled":"true","hardware_model":"x86_64","processors_count":"8"},1.2.3.4,0.0.0.0,abc1103.xyz.com,1536923595397,x86_64,,redhat7,/926,61680136

I have tried following DDL:

create external table hosts ( host_id int,hostname string,cpu_count int, ph_cpu_count int, cpu_info string, discovery_status string, host_attributes MAP<string,string>, ipv4 string, ipv6 string, public_host_name string,last_registration_time bigint,os_arch string,os_info string, os_type string, rack_info string, total_mem bigint )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '\n' location '/user/test'

I also tried by altering host_attributes by executing following query:

alter table hosts change host_attributes host_attributes struct<interfaces:string,os_family:string,kernel:string,timezonetimezone:string,kernel_release:string,os_release_version:float,physicalprocessors_count:int,hardware_isa:string, kernel_majorversion:string,kernel_version:string, netmask:string, mac_address:string, swap_free:string, swap_size:string,selinux_enabled:string,hardware_model:string,processors_count:int> after discovery_status;

What is appropriate Hive DDL for this data.

Thanks in Advance!

1 REPLY 1

Re: HIVE DDL for complex type CSV containing Json obejct

New Contributor

@Artem Ervits

Could you please help me here.