FAQ

[Hive-user] insert data into a column of complex type

Ping Zhu
Sep 24, 2010 at 12:12 am
Hi,

Can we insert data into a column of complex type (eg map type) through
hive command line? The only related article I can find is at
http://www.mail-archive.com/common-commits@hadoop.apache.org/msg01031.html:
"The tables with columns that are an instance of a complex type can only
be created programmatically and NOT through hive command line at this
time'''. We will be adding ability to add such tables through the hive
command line in the future. "

Any new progress on this feature?

Thanks for your information and help.

Ping
reply

Search Discussions

3 responses

  • Ning Zhang at Sep 24, 2010 at 5:07 pm
    Ping, you cannot insert complex type column by manually input the data. You'll need to have the complex typed data stored in some file first (e.g., using Thrift serialization or Protocol Buffers), and then load into the table.

    Another option you have is to use JSON/XML to represent map. Hive has UDF/UDTF to support extracting data from JSON/XML (json_object, json_tuple, xpath_string, xpath_int ...). hive> show functions; and hive> desc <func_name> will give you more info about these UDFs.
    On Sep 23, 2010, at 5:12 PM, Ping Zhu wrote:

    Hi,

    Can we insert data into a column of complex type (eg map type) through hive command line? The only related article I can find is at http://www.mail-archive.com/common-commits@hadoop.apache.org/msg01031.html: "The tables with columns that are an instance of a complex type can only be created programmatically and NOT through hive command line at this time'''. We will be adding ability to add such tables through the hive command line in the future. "

    Any new progress on this feature?

    Thanks for your information and help.

    Ping
  • John Sichi at Sep 24, 2010 at 5:53 pm
    There are also undocumented "constructor" functions named map, struct, and array. These instantiate complex types directly:


    hive> describe function map;
    OK
    map(key0, value0, key1, value1...) - Creates a map with the given key/value pairs
    hive> describe function struct;
    OK
    struct(col1, col2, col3, ...) - Creates a struct with the given field values
    Time taken: 0.072 seconds
    hive> describe function array;
    OK
    array(n0, n1...) - Creates an array with the given elements
    Time taken: 0.071 seconds

    I'll add them to the wiki.

    JVS

    On Sep 24, 2010, at 10:10 AM, Ning Zhang wrote:

    Ping, you cannot insert complex type column by manually input the data. You'll need to have the complex typed data stored in some file first (e.g., using Thrift serialization or Protocol Buffers), and then load into the table.

    Another option you have is to use JSON/XML to represent map. Hive has UDF/UDTF to support extracting data from JSON/XML (json_object, json_tuple, xpath_string, xpath_int ...). hive> show functions; and hive> desc <func_name> will give you more info about these UDFs.

    On Sep 23, 2010, at 5:12 PM, Ping Zhu wrote:

    Hi,

    Can we insert data into a column of complex type (eg map type) through hive command line? The only related article I can find is at http://www.mail-archive.com/common-commits@hadoop.apache.org/msg01031.html: "The tables with columns that are an instance of a complex type can only be created programmatically and NOT through hive command line at this time'''. We will be adding ability to add such tables through the hive command line in the future. "

    Any new progress on this feature?

    Thanks for your information and help.

    Ping
  • Ping Zhu at Sep 24, 2010 at 5:56 pm
    Thank you, Ning and John for your valuable information. I will try map
    function first:)

    Ping
    On Fri, Sep 24, 2010 at 10:53 AM, John Sichi wrote:

    There are also undocumented "constructor" functions named map, struct, and
    array. These instantiate complex types directly:


    hive> describe function map;
    OK
    map(key0, value0, key1, value1...) - Creates a map with the given key/value
    pairs
    hive> describe function struct;
    OK
    struct(col1, col2, col3, ...) - Creates a struct with the given field
    values
    Time taken: 0.072 seconds
    hive> describe function array;
    OK
    array(n0, n1...) - Creates an array with the given elements
    Time taken: 0.071 seconds

    I'll add them to the wiki.

    JVS

    On Sep 24, 2010, at 10:10 AM, Ning Zhang wrote:

    Ping, you cannot insert complex type column by manually input the data.
    You'll need to have the complex typed data stored in some file first (e.g.,
    using Thrift serialization or Protocol Buffers), and then load into the
    table.

    Another option you have is to use JSON/XML to represent map. Hive has
    UDF/UDTF to support extracting data from JSON/XML (json_object, json_tuple,
    xpath_string, xpath_int ...). hive> show functions; and hive> desc
    <func_name> will give you more info about these UDFs.

    On Sep 23, 2010, at 5:12 PM, Ping Zhu wrote:

    Hi,

    Can we insert data into a column of complex type (eg map type) through
    hive command line? The only related article I can find is at
    http://www.mail-archive.com/common-commits@hadoop.apache.org/msg01031.html:
    "The tables with columns that are an instance of a complex type can only
    be created programmatically and NOT through hive command line at this
    time'''. We will be adding ability to add such tables through the hive
    command line in the future. "

    Any new progress on this feature?

    Thanks for your information and help.

    Ping


Related Discussions

Discussion Navigation
viewthread | post