Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 303AF17A48 for ; Tue, 6 Jan 2015 01:07:53 +0000 (UTC) Received: (qmail 90133 invoked by uid 500); 6 Jan 2015 01:07:51 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 90057 invoked by uid 500); 6 Jan 2015 01:07:51 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 90047 invoked by uid 99); 6 Jan 2015 01:07:51 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Jan 2015 01:07:51 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of gates@hortonworks.com designates 209.85.220.49 as permitted sender) Received: from [209.85.220.49] (HELO mail-pa0-f49.google.com) (209.85.220.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Jan 2015 01:07:24 +0000 Received: by mail-pa0-f49.google.com with SMTP id eu11so29734129pac.36 for ; Mon, 05 Jan 2015 17:07:23 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to :subject:references:in-reply-to:content-type; bh=ICAubaYcQEWD0UTMncd2oJa/JkOzkVHu2lRJ/W3XVmo=; b=aMlXLwpg8IzYC8XRvhulXO2aTkNw+fmtOTAJbYjqOuC4r58hpJsKhNjlat1i9fzFYE vNLAOL1675+McB934H8s9e5Ar/EpAnsAO2p7G8dYuOoKAf2iuVxxY4j3OIYmBXH7fxya lE6/jeBnUWe7bpSolTybqvsaDWGpw3a5EfTgdbY18YMqKknfLivT5+vRim/TDqjrE/Yd qsthRLlIj6imMt9rIGf2CNev1pnvZ2cHs4xkmv4mVywO20iR/BhxwSG0EMJAP1Xo/pqD q+Ux/K/QHc4HpvkRkRRwtLqcLAG3YG1EKxL3sB6wyRV1sdnUcTbfVKV4nPBITFxN58Wa DVcQ== X-Gm-Message-State: ALoCoQli4ELt9QUobWqb9cyWIW+EOIGme8DvlD2CXE/vl+EWWnoTvwNNOuoDlAWdSFaxNhP/gdKs8QTR1bt5EuisLba8rzVpnxMy+PpBGISFDd2pdijTEJk= X-Received: by 10.68.238.234 with SMTP id vn10mr55682537pbc.140.1420506442755; Mon, 05 Jan 2015 17:07:22 -0800 (PST) Received: from Alan-Gatess-MacBook-Pro.local (outbound.hortonworks.com. [192.175.27.2]) by mx.google.com with ESMTPSA id nv7sm55286572pbc.29.2015.01.05.17.07.20 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 05 Jan 2015 17:07:21 -0800 (PST) Message-ID: <54AB3546.40004@hortonworks.com> Date: Mon, 05 Jan 2015 17:07:18 -0800 From: Alan Gates User-Agent: Postbox 3.0.11 (Macintosh/20140602) MIME-Version: 1.0 To: user@hive.apache.org Subject: Re: Hive 0.14 SQL Inserts Complex Data Types References: <9113b0da8ff044d0a88f2cd5c70bb0f4@mockingbird.livetv-ifs.com> <549453CD.1080106@hortonworks.com> <552A0DBF-9906-47F2-9A2C-DEAF5152BDFD@hortonworks.com> In-Reply-To: <552A0DBF-9906-47F2-9A2C-DEAF5152BDFD@hortonworks.com> Content-Type: multipart/related; boundary="------------010208020105090806080006" X-Virus-Checked: Checked by ClamAV on apache.org --------------010208020105090806080006 Content-Type: multipart/alternative; boundary="------------010206040905060901020502" This is a multi-part message in MIME format. --------------010206040905060901020502 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: quoted-printable No, because insert...values writes the values to a local file, builds a=20 temp table over that local file, then rewrites the query to "insert into=20 X select from tmptable". The expressions aren't evaluated before being=20 written to the file. More work would need to be done to write the=20 "pre-expression" data to the file and then have the query re-write=20 handle inserting the expressions back into the insert statement. Alan. > Jason Dere > December 19, 2014 at 10:55 > There are UDFs to create complex types, would something like this work? > INSERT .. VALUES select map('key1', 'val1', 'key2', 'val2'); > > > > > > CONFIDENTIALITY NOTICE > NOTICE: This message is intended for the use of the individual or=20 > entity to which it is addressed and may contain information that is=20 > confidential, privileged and exempt from disclosure under applicable=20 > law. If the reader of this message is not the intended recipient, you=20 > are hereby notified that any printing, copying, dissemination,=20 > distribution, disclosure or forwarding of this communication is=20 > strictly prohibited. If you have received this communication in error,=20 > please contact the sender immediately and delete it from your system.=20 > Thank You. > Skyler Beckman > December 19, 2014 at 8:50 > > Thanks > > *From:*Alan Gates [mailto:gates@hortonworks.com] > *Sent:* Friday, December 19, 2014 11:35 AM > *To:* user@hive.apache.org > *Subject:* Re: Hive 0.14 SQL Inserts Complex Data Types > > Hive does not support literals for complex types, so it is not=20 > possible to use them in INSERT...VALUES clauses. > > Alan. > > > *Skyler Beckman* > > December 17, 2014 at 9:59 > > With 0.14 is it possible to use the INSERT=85VALUES statement to insert= =20 > data into a table with complex types like arrays, maps, etc? We will=20 > never be loading data from a file which is why I ask. > > --=20 > > Sent with Postbox > > > CONFIDENTIALITY NOTICE > NOTICE: This message is intended for the use of the individual or=20 > entity to which it is addressed and may contain information that is=20 > confidential, privileged and exempt from disclosure under applicable=20 > law. If the reader of this message is not the intended recipient, you=20 > are hereby notified that any printing, copying, dissemination,=20 > distribution, disclosure or forwarding of this communication is=20 > strictly prohibited. If you have received this communication in error,=20 > please contact the sender immediately and delete it from your system.=20 > Thank You. > > Alan Gates > December 19, 2014 at 8:35 > Hive does not support literals for complex types, so it is not=20 > possible to use them in INSERT...VALUES clauses. > > Alan. > > > Skyler Beckman > December 17, 2014 at 9:59 > > With 0.14 is it possible to use the INSERT=85VALUES statement to insert= =20 > data into a table with complex types like arrays, maps, etc? We will=20 > never be loading data from a file which is why I ask. > --=20 Sent with Postbox --=20 CONFIDENTIALITY NOTICE NOTICE: This message is intended for the use of the individual or entity to= =20 which it is addressed and may contain information that is confidential,=20 privileged and exempt from disclosure under applicable law. If the reader= =20 of this message is not the intended recipient, you are hereby notified that= =20 any printing, copying, dissemination, distribution, disclosure or=20 forwarding of this communication is strictly prohibited. If you have=20 received this communication in error, please contact the sender immediately= =20 and delete it from your system. Thank You. --------------010206040905060901020502 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable No, because=20 insert...values writes the values to a local file, builds a temp table=20 over that local file, then rewrites the query to "insert into X select=20 from tmptable".=A0 The expressions aren't evaluated before being written=20 to the file.=A0 More work would need to be done to write the=20 "pre-expression" data to the file and then have the query re-write=20 handle inserting the expressions back into the insert statement.

Alan.

= = =20 December 19, 201= 4 at 10:55
There are UDFs to create complex types, would something like this work?
=A0 =A0 INSERT .. VALUES select=20 map('key1', 'val1', 'key2', 'val2');




CONFIDENTIALITY NOTICENOTICE: This message is intended for the use of the individual or entity to=20 which it is addressed and may contain information that is confidential,=20 privileged and exempt from disclosure under applicable law. If the=20 reader of this message is not the intended recipient, you are hereby=20 notified that any printing, copying, dissemination, distribution,=20 disclosure or forwarding of this communication is strictly prohibited.=20 If you have received this communication in error, please contact the=20 sender immediately and delete it from your system. Thank You.
= = =20 December 19, 201= 4 at 8:50

Thanks

=A0<= /p>

From: Alan Gates [mailto:gates@hortonworks.com]
Sent: Friday, December 19, 2014 11:35 AM
To: user@hive.apache.org
Subject: Re: Hive 0.14 SQL Inserts Complex Data Types

=A0

Hive does not support literals for complex types,=20 so it is not possible to use them in INSERT...VALUES clauses.

Alan.


Skyle= r Beckman

December 17, 2014 at 9:59

With 0.14 is it possible to use the INSERT=85VALUES= =20 statement to insert data into a table with complex types like arrays,=20 maps, etc? We will never be loading data from a file which is why I ask.

=A0

=A0

--

Sent with Postbox


CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is=20 confidential, privileged and exempt from disclosure under applicable=20 law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying,=20 dissemination, distribution, disclosure or forwarding of this=20 communication is strictly prohibited. If you have received this=20 communication in error, please contact the sender immediately and delete it from your system. Thank You.

Alan Gates = =20 December 19, 201= 4 at 8:35
Hive does not support=20 literals for complex types, so it is not possible to use them in=20 INSERT...VALUES clauses.

Alan.


= = =20 December 17, 201= 4 at 9:59

With 0.14 is it possible to use the INSERT=85VALUES= =20 statement to insert data into a table with complex types like arrays,=20 maps, etc? We will never be loading data from a file which is why I ask.

=A0


--
Sent with Postbox

CONFIDENTIALITY NOTICE
NOTICE: This message is = intended for the use of the individual or entity to which it is addressed a= nd may contain information that is confidential, privileged and exempt from= disclosure under applicable law. If the reader of this message is not the = intended recipient, you are hereby notified that any printing, copying, dis= semination, distribution, disclosure or forwarding of this communication is= strictly prohibited. If you have received this communication in error, ple= ase contact the sender immediately and delete it from your system. Thank Yo= u. --------------010206040905060901020502-- --------------010208020105090806080006 Content-Type: image/jpeg; x-apple-mail-type=stationery; name="compose-unknown-contact.jpg" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="compose-unknown-contact.jpg" /9j/4AAQSkZJRgABAQEARwBHAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEC AQEBAQEBAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/2wBDAQEBAQEBAQICAgICAgIC AgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/wAAR CAAZABkDAREAAhEBAxEB/8QAGAAAAwEBAAAAAAAAAAAAAAAABgcICQr/xAA0EAABAwMCAgUK BwAAAAAAAAACAQMEBQYRABITIQcUMUF2CBUXIjI2N0JRtVRWkZOV0dL/xAAYAQEAAwEAAAAA AAAAAAAAAAADAAEEAv/EACQRAAICAAQGAwAAAAAAAAAAAAABAhEDMrHREyExM0FxgfDx/9oA DAMBAAIRAxEAPwDuEt+gW/ULet6oVC3rfqNQqFv0OfPn1GhUqfOmzZtKZlS5UqZMaNwzNwiJ VIl7eXLCaZIGwBl3TY8epPx2+jy2ZNPjvkwc9uhW8j7nCPhvOsQliYIeS7cvCpp8o50qwrC4 v3lsNSDbdmTEhvs2tahxpfV3WnmbbozJEw/gwdadbYExVRXKEKoSdvJcaOSqxE7/AAiX0gXx +a69/JSf9alIlste0VzaNpeFrcT9KKymotyiaZ0KRCnzacoE7Kjzn4gi2KqUh3jqDHDHv4mR UfruTWlMzlVUKIVNp9GguEJnAh0+IZjyAiisgyRDnu5azS8miKqjOTVkKqS/psG37fo1Fbab eg25b8eZPeFJBBJSjMG5HjMeyihnaauZwe4OGiju13GAcpOwBeN+U8/IkGbsiS8b7ryogmbz hbyc9REROfZhERO5ETShjPtvpGqTUyLErytS4siSwx5x2tRH4hPOI0DkjZtaJtFxuVEbIUUi yeNujlBUJGbJN6nM/Cyf2Hf60YgjvKA+NPSP4gT7axpcPtr51YWJnYn9dnAQWl722p4ot37y zqnlfp6FrqbwawG8/9k= --------------010208020105090806080006 Content-Type: image/jpeg; name="image.jpg" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="image.jpg" /9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAMCAgICAgMCAgIDAwMDBAYEBAQEBAgGBgUGCQgK CgkICQkKDA8MCgsOCwkJDRENDg8QEBEQCgwSExIQEw8QEBD/2wBDAQMDAwQDBAgEBAgQCwkL EBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBD/wAAR CAAZABkDAREAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAA AgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkK FhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWG h4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl 5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREA AgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYk NOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOE hYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk 5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD9LNP0/T7jT7S4uNPtJpZbeKSSSSBHZ2KA kkkZJzVWIMqXVdDjuWjXw7ZPEjbTIII8nHUgbf61XKLmNyDTdFm8t00uxKSYIYWyDg9wcVLR R5v/AMJBrn/QWuv+/ppiPR9I/wCQRYf9esP/AKAKQHGzwSQXEluQWdHKDH8Rzx+dWSdzYwm2 t7eBiCYkRCfoAKllI8hoA7j+35oNPtLOyVQ0VtEryMM4OwZAH+NNITZluzyu0kjFndixbuSa Yja03xHcpMkN+VkR2C+ZgKVycc9sflSaHc4TyZv+eT/98mkM5n4gf8jrrH/Xx/7KtNbCe5z9 MRb0j/kLWH/X1D/6GKHsB9QVkan/2Q== --------------010208020105090806080006 Content-Type: image/jpeg; x-apple-mail-type=stationery; name="postbox-contact.jpg" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="postbox-contact.jpg" /9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAMCAgICAgMCAgIDAwMDBAYEBAQEBAgGBgUGCQgK CgkICQkKDA8MCgsOCwkJDRENDg8QEBEQCgwSExIQEw8QEBD/2wBDAQMDAwQDBAgEBAgQCwkL EBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBD/wAAR CAAZABkDAREAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAA AgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkK FhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWG h4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl 5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREA AgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYk NOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOE hYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk 5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD9AtevtF8P/DO7+IOraNaX89pox1OUzQq7 zy+TuwWIJ5YgewNdEI8zSPMqNr3j8v8Axh4s8UeONZn8Q+Prmxv7t2f7Pb/2fbiC0jJzsjXZ wOg9eOcnmu5yjT92JrQo80eaW51X7NXxQ1X4f/EvTYHawutC1O9t7PULQ6fDG6RSOIxLG6KG Vo2ZW4PQGpny1Itk1KUqTuj7V/4WX4x/6GC4/wDHf8K8+yKvPuUf2oL7xDb/ALMvh7+wbiWK 3vn0q21Rozz9keE5BOPulxHmqpv3jo9nFppn5y+MLrV5b9IdNlgj3Ps/eDsO/wDkV0Xcmawp vaJt/s+6RqXi/wCPnhXwj9nPlXGrw7plGR5KfvJCR/uI1KT5Y3JqJvRo+wv7cj/vD864+bzI 5V2PIvjp+2XrUNno3wC0e2sJ7Q6Na2/iC7kiYSwzLCrC2jBI2ujKu8sDySoAIJreFFwXPLcS kpztF6Hy/wCINQF4s13FOMBNqAgHJJ569KtNrU6os9g/YetpNM8fXvxJ1EyW+n+H7SaCG42b l+1SgBsE8fLEXJPbevrSlRq1V7qOWviqVF8s2dl/anjf/oX/ABJ/4KpP/iax9jPyF7aHZny7 +2p/yd98Qv8AsLr/AOk8Va1PhQ6XxHjt11P+8anobdT3n9j/AP5Dtn/2M1n/AOhRVvD4Dgr/ AMRH7UVzHQf/2Q== --------------010208020105090806080006--