Re: HBase table map to hive

[naga sharathrayapati]

If your schema is changing frequently I think it's better to use Avro for
schema evolution, and go with 'Avro Serde' with 'AvroContainerInputFormat'
&  'AvroContaineOutputFormat' for creating table in Hive from Hbase.

On Mon, Apr 4, 2016 at 1:19 PM, Wojciech Indyk 
wrote:

> Hi!
> You can use map on your column family or a prefix of
> column qualifier.
>
> https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration#HBaseIntegration-HiveMAPtoHBaseColumnFamily
>
> --
> Kind regards/ Pozdrawiam,
> Wojciech Indyk
> http://datacentric.pl
>
>
> 2016-04-04 14:13 GMT+02:00 ram kumar :
> > Hi,
> >
> > I have a hbase table with column name changes (increases) over time.
> > Is there a way to map such hbase to hive table,
> > inferring schema from the hbase table?
> >
> > Thanks
>

Re: Converting date format and excel money format in Hive table

[naga sharathrayapati]

I think 'translate' might be useful in this scenario

example:
select translate(t2.net,'?','$') from t2;

On Fri, Jan 15, 2016 at 5:05 AM, Mich Talebzadeh 
wrote:

> Thanks that solved data conversion.
>
>
>
> How does one replace  ?10,000.00 with £10,000.00 ?
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> *Sybase ASE 15 Gold Medal Award 2008*
>
> A Winning Strategy: Running the most Critical Financial Data on ASE 15
>
>
> http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE
> 15", ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Complex Event Processing in Heterogeneous Environments*, ISBN:
> 978-0-9563693-3-8
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume
> one out shortly
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Technology
> Ltd, its subsidiaries or their employees, unless expressly so stated. It is
> the responsibility of the recipient to ensure that this email is virus
> free, therefore neither Peridale Technology Ltd, its subsidiaries nor their
> employees accept any responsibility.
>
>
>
> *From:* matshyeq [mailto:matsh...@gmail.com]
> *Sent:* 15 January 2016 10:31
> *To:* user 
> *Subject:* Re: Converting date format and excel money format in Hive table
>
>
>
> try:
> select cast(unix_timestamp('02/10/2014', 'dd/MM/')*1000 as timestamp);
>
> Kind Regards
>
> ~Maciek
>
> On 15 January 2016 at 10:15, Mich Talebzadeh  wrote:
>
> Hi,
>
>
>
>
>
> I am importing an excel sheet saved as csv file comma separated and
> compressed with bzip2 into Hive as external table with bzip2
>
>
>
> The excel looks like this
>
>
>
> *Invoice Number*
>
> *Payment date*
>
> *Net*
>
> *VAT*
>
> *Total*
>
> 360
>
> *10/02/2014*
>
> £10,000.00
>
> £2000.00
>
> £12,000.00
>
>
>
> And the file (before bzip2) looks like this
>
>
>
> Invoice Number,Payment date,Net,VAT,Total
>
> 360,10/02/2014,"▒12,000.00",▒2000.00,"▒12,000.00"
>
>
>
>
>
> The external table is defined as
>
>
>
> CREATE EXTERNAL TABLE stg_t2 (
>
> INVOICENUMBER string
>
> ,PAYMENTDATE string
>
> ,NET string
>
> ,VAT string
>
> ,TOTAL string
>
> )
>
> COMMENT 'from csv file from excel sheet ‘
>
> ROW FORMAT serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
>
> STORED AS TEXTFILE
>
> LOCATION '/xyz/'
>
> TBLPROPERTIES ("skip.header.line.count"="1")
>
> ;
>
>
>
>
>
> And the table itself
>
>
>
>
>
> CREATE TABLE t2 (
>
> INVOICENUMBER  INT
>
> ,PAYMENTDATEstring
>
> ,NETstring
>
> ,VATstring
>
> ,TOTAL  string
>
> )
>
> COMMENT 'from csv file from excel sheet'
>
> STORED AS ORC
>
> TBLPROPERTIES ( "orc.compress"="ZLIB" )
>
> ;
>
> INSERT INTO TABLE t2
>
> SELECT
>
>   INVOICENUMBER
>
> , PAYMENTDATE
>
> , NET
>
> , VAT
>
> , TOTAL
>
> FROM
>
> stg_t2;
>
>
>
>
>
> Now the problem I have is that I do not seem to be able to convert
> PAYMENTDATE into timestamp (from string) using CAST (PAYMENDATE AS
> TIMESTAMP) it RFETURNS NULL. Also I would like to store currency properly
>  replacing “?”  with “£”?
>
>
>
>
> +---+-+--+-+--+--+
>
> | t2.invoicenumber  | t2.paymentdate  |t2.net|   t2.vat|
> t2.total   |
>
>
> +---+-+--+-+--+--+
>
> | 360   | 10/02/2014  | ?10,000.00   | ?2000.00|
> ?12,000.00   |
>
> |
>
>
>
> Thanks
>
>
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> *Sybase ASE 15 Gold Medal Award 2008*
>
> A Winning Strategy: Running the most Critical Financial Data on ASE 15
>
>
> http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE
> 15", ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Complex Event Processing in Heterogeneous Environments*, ISBN:
> 978-0-9563693-3-8
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: