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 the given csv data

Highlighted

hive DDL for the given csv data

New Contributor

Not able to parse field "Starting Lat-Long" with "struct<longitude:string,latitude:string,needs_recoding:string>"

hive> describe formatted hivelearn1;
OK
col_name        data_type       comment
# col_name              data_type               comment


trip_id                 int
duration                bigint
starttime               string
endtime                 string
starting_station_id     bigint
start_station_lat       double
start_st_long           double
ending_st_id            bigint
ending_st_lat           double
ending_st_long          double
bike_id                 int
plan_duration           bigint
trip_route              string
pass_type               string
start_lat_long          struct<longitude:string,latitude:string,needs_recoding:string>


# Detailed Table Information
Database:               shareride
Owner:                  hdf62-hdfs
CreateTime:             Thu Oct 04 07:12:16 BST 2018
LastAccessTime:         UNKNOWN
Protect Mode:           None
Retention:              0
Location:               hdfs://hdf62/apps/hive/warehouse/shareride.db/motorshare
Table Type:             MANAGED_TABLE
Table Parameters:
        COLUMN_STATS_ACCURATE   false
        last_modified_by        hdf62-hdfs
        last_modified_time      1538636103
        numFiles                1
        numRows                 -1
        rawDataSize             -1
        skip.header.line.count  1
        totalSize               36376430
        transient_lastDdlTime   1538636103


# Storage Information
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat:            org.apache.hadoop.mapred.TextInputFormat
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed:             No
Num Buckets:            -1
Bucket Columns:         []
Sort Columns:           []
Storage Desc Params:
        field.delim             ,
        serialization.format    ,


Trip ID Duration Start Time End Time Starting Station ID Starting Station Latitude Starting Station Longitude Ending Station ID Ending Station Latitude Ending Station Longitude Bike ID Plan Duration Trip Route Category Passholder Type Starting Lat-Long
1912818 180 2016-07-07T04:17:00 2016-07-07T04:20:00 3014 34.05661 -118.237 3014 34.05661 -118.237 6281 30 Round Trip Monthly Pass {'longitude': '-118.23721', 'latitude': '34.0566101', 'needs_recoding': False}
1919661 1980 2016-07-07T06:00:00 2016-07-07T06:33:00 3014 34.05661 -118.237 3014 34.05661 -118.237 6281 30 Round Trip Monthly Pass {'longitude': '-118.23721', 'latitude': '34.0566101', 'needs_recoding': False}
1933383 300 2016-07-07T10:32:00 2016-07-07T10:37:00 3016 34.052898 -118.242 3016 34.0529 -118.242 5861 365 Round Trip Flex Pass {'longitude': '-118.24156', 'latitude': '34.0528984', 'needs_recoding': False}
5 REPLIES 5

Re: hive DDL for the given csv data

New Contributor
@Nitin Chunke

could you please help me with this

Re: hive DDL for the given csv data

New Contributor

getting null values while parsing like "{"longitude":"\"{'longitude': '-118.23721'","latitude":null,"needs_recoding":null}"

Re: hive DDL for the given csv data

New Contributor

@Artem Ervits could you please help me

Re: hive DDL for the given csv data

New Contributor

Can you try

start_lat_long array<struct<longitude:string,latitude:string,needs_recoding:string>>

Re: hive DDL for the given csv data

New Contributor