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

hive DDL for the given csv data

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

hive> describe formatted hivelearn1;
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:
        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
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}

@Nitin Chunke

could you please help me with this

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

@Artem Ervits could you please help me

New Contributor

Can you try

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

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