Return-Path: X-Original-To: apmail-hadoop-user-archive@minotaur.apache.org Delivered-To: apmail-hadoop-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id CEAE917793 for ; Fri, 1 May 2015 05:34:21 +0000 (UTC) Received: (qmail 12305 invoked by uid 500); 1 May 2015 05:34:16 -0000 Delivered-To: apmail-hadoop-user-archive@hadoop.apache.org Received: (qmail 12204 invoked by uid 500); 1 May 2015 05:34:16 -0000 Mailing-List: contact user-help@hadoop.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hadoop.apache.org Delivered-To: mailing list user@hadoop.apache.org Received: (qmail 12189 invoked by uid 99); 1 May 2015 05:34:16 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 01 May 2015 05:34:16 +0000 X-ASF-Spam-Status: No, hits=2.4 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: message received from 54.164.171.186 which is an MX secondary for user@hadoop.apache.org) Received: from [54.164.171.186] (HELO mx1-us-east.apache.org) (54.164.171.186) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 01 May 2015 05:34:10 +0000 Received: from mail-oi0-f45.google.com (mail-oi0-f45.google.com [209.85.218.45]) by mx1-us-east.apache.org (ASF Mail Server at mx1-us-east.apache.org) with ESMTPS id 839B0473D6 for ; Fri, 1 May 2015 05:23:43 +0000 (UTC) Received: by oift201 with SMTP id t201so64252823oif.3 for ; Thu, 30 Apr 2015 22:23:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=Gi40J0w53n6FkD/5t/dppOaVTotqSMzxn+LTk7kNqCM=; b=s0pscOxuA7vNy3cISkgwMLP35PSU6PiiI0oKsPAMRWLWh7xm5rRljqJLq0j/Htdo9l VljI9c0FtfXeUwUwrvGFkWVfymvzmo9cV8tJK1+KcupiTJdMfgBViYAxlGz2RO52WhwT aTLy7tb9gG74/tZmtDOv+Sd6NJBun2/sPkXxAJ3Bkmb33nFa407LrR83ZmluCUc3AKEM PmgICzF6poUk/WsUHoiJWjjmWY2EJQMn58XxL0Plwtn91sKhIjhkmhvFDDDstmUExr95 N2t00FUT14lPrDlMdHHQQN0W0Zyza00F5OfkIdVy0AZh6YZEbPw+VJ80bGX5wXn3+P/F QOag== MIME-Version: 1.0 X-Received: by 10.60.84.65 with SMTP id w1mr6546434oey.2.1430457822970; Thu, 30 Apr 2015 22:23:42 -0700 (PDT) Received: by 10.202.88.195 with HTTP; Thu, 30 Apr 2015 22:23:42 -0700 (PDT) In-Reply-To: References: Date: Thu, 30 Apr 2015 22:23:42 -0700 Message-ID: Subject: Re: how to load data From: Kumar Jayapal To: user@hadoop.apache.org Content-Type: multipart/alternative; boundary=089e0111b92a6789040514fe6a66 X-Virus-Checked: Checked by ClamAV on apache.org --089e0111b92a6789040514fe6a66 Content-Type: text/plain; charset=UTF-8 Alex, I followed the same steps as mentioned in the site. Once I load data into table which is create below Created table CREATE TABLE raw (line STRING) PARTITIONED BY (FISCAL_YEAR smallint, FISCAL_PERIOD smallint) STORED AS TEXTFILE; and loaded it with data. LOAD DATA LOCAL INPATH '/tmp/weblogs/20090603-access.log.gz' INTO TABLE raw; when I say select * from raw it shows all null values. NULLNULLNULLNULLNULLNULLNULLNULL NULLNULLNULLNULLNULLNULLNULLNULL NULLNULLNULLNULLNULLNULLNULLNULL NULLNULLNULLNULLNULLNULLNULLNULL Why is not show showing the actual data in file. will it show once I load it to parque table? Please let me know if I am doing anything wrong. I appreciate your help. Thanks jay Thank you very much for you help Alex, On Wed, Apr 29, 2015 at 3:43 PM, Alexander Pivovarov wrote: > 1. Create external textfile hive table pointing to /extract/DBCLOC and > specify CSVSerde > > if using hive-0.14 and newer use this > https://cwiki.apache.org/confluence/display/Hive/CSV+Serde > if hive-0.13 and older use https://github.com/ogrodnek/csv-serde > > You do not even need to unzgip the file. hive automatically unzgip data on > select. > > 2. run simple query to load data > insert overwrite table > select * from > > On Wed, Apr 29, 2015 at 3:26 PM, Kumar Jayapal > wrote: > >> Hello All, >> >> >> I have this table >> >> >> CREATE TABLE DBCLOC( >> BLwhse int COMMENT 'DECIMAL(5,0) Whse', >> BLsdat string COMMENT 'DATE Sales Date', >> BLreg_num smallint COMMENT 'DECIMAL(3,0) Reg#', >> BLtrn_num int COMMENT 'DECIMAL(5,0) Trn#', >> BLscnr string COMMENT 'CHAR(1) Scenario', >> BLareq string COMMENT 'CHAR(1) Act Requested', >> BLatak string COMMENT 'CHAR(1) Act Taken', >> BLmsgc string COMMENT 'CHAR(3) Msg Code') >> PARTITIONED BY (FSCAL_YEAR smallint, FSCAL_PERIOD smallint) >> STORED AS PARQUET; >> >> have to load from hdfs location /extract/DBCLOC/DBCL0301P.csv.gz to the >> table above >> >> >> Can any one tell me what is the most efficient way of doing it. >> >> >> Thanks >> Jay >> > > --089e0111b92a6789040514fe6a66 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Alex,


I followed the sam= e steps as mentioned in the site. Once I load data into table which is crea= te below



Created table =C2=A0CREATE TABLE raw (line STRING)=C2=A0PARTI= TIONED BY (FISCAL_YEAR =C2=A0smallint, FISCAL_PERIOD smallint)
STORED= AS=C2=A0TEXTFILE;

and loaded it = with data.

LOAD DATA LOCAL INP= ATH=C2=A0'/tmp/weblogs/20090603-access.log.gz= 9;=C2=A0INT= O TABLE raw;


= when I say select * from raw it shows all null values.


NULLNULLNULLNULLNULLNULLNULLNULL

NULLNULLNULLNULLNULLNULLNUL= LNULL

NULLNULLNULLNULLNULLNULLNULLNULL

NULLNULLNULLNULLNULLNULLNULLNULL
Why= is not show showing the actual data in file. will it show once I load it t= o parque table?

Please let me know if I am doing anything = wrong.

I appreciate your help.

<= /div>
=
Thanks
jay=C2=A0


=

Thank you very much for you help Alex,


O= n Wed, Apr 29, 2015 at 3:43 PM, Alexander Pivovarov <<= a href=3D"mailto:apivovarov@gmail.com" target=3D"_blank">apivovarov@gmail.c= om> wrote:
1. Create external textfile hive table pointing to /extr= act/DBCLOC and specify CSVSe= rde

if using hive-0.14 and newer use this https://cwiki.apache.org/confluence/display/Hive/CSV+Serde<= br>
if hive-0.13 and older use https://github.com/ogrodnek/csv-serde
<= br>You do not even need to unzgip the file. hive automatically unzgip data = on select.

2. run simple query to load data
insert ov= erwrite table <orc_table>
select * from <csv_table>
<= br>
On Wed, Apr 29, 2015 at 3:26 PM, Kumar Jayapa= l <kjayapal17@gmail.com> wrote:
Hello All,


I have this table


CREATE =C2=A0TABLE DBCL= OC( =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0 =C2=A0BLwhse int COMMENT 'DECIMAL(5,0)= Whse', =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0
=C2=A0 =C2=A0BLsdat string COMMENT 'DATE Sales Date', =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0
=C2=A0= =C2=A0BLreg_num smallint COMMENT 'DECIMAL(3,0) Reg#', =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0 =C2=A0BLtrn_num int COMMENT 'D= ECIMAL(5,0) Trn#', =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0=C2=A0
=C2=A0 =C2=A0BLscnr string COMMENT 'CHAR(1) Scenario', = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0
=C2=A0 = =C2=A0BLareq string COMMENT 'CHAR(1) Act Requested', =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=C2=A0 =C2=A0BLatak string COMMENT '= CHAR(1) Act Taken', =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0=C2=A0
=C2=A0 =C2=A0BLmsgc string COMMENT 'CHAR(3) Msg Code'= ) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0
PARTITION= ED BY (FSCAL_YEAR =C2=A0smallint, FSCAL_PERIOD smallint)
<= span style=3D"font-size:12.8000001907349px">STORED AS PARQUET;
=

have to load from hdfs location =C2=A0/extract/DBCLOC/DBCL0301P.csv.gz to the table above=C2=A0
<= div>

Can any one tell me what is the most effi= cient way of doing it.


Thanks
Jay


--089e0111b92a6789040514fe6a66--