Support Questions
Find answers, ask questions, and share your expertise

HIVE DDL for complex type CSV containing Json obejct

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

@Artem Ervits

Could you please help me here.

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.