无法从配置单元表的列中提取产品价格

vmdwslir 于 2021-06-26 发布在 Hive

关注(0)|答案(2)|浏览(399)

我正在尝试将数据从配置单元中的临时表加载到常规表中。下面是临时表的前几行。

ProdNo,ProdName,ProdMfg,ProdQOH,ProdPrice,ProdNextShipDate
P0036566,17 inch Color Monitor,ColorMeg Inc.,12,$169.00,2013-02-20
P0036577,19 inch Color Monitor,ColorMeg Inc.,10,$319.00,2013-02-20
P1114590,R3000 Color Laser Printer,Connex,5,$699.00,2013-01-22

我正在使用下面的代码这样做

insert overwrite table product
SELECT  
  regexp_extract(col_value, '^(?:([^,]*),?){1}', 1) ProdNo,  
  regexp_extract(col_value, '^(?:([^,]*),?){2}', 1) ProdName,  
  regexp_extract(col_value, '^(?:([^,]*),?){3}', 1) ProdMfg,
  regexp_extract(col_value, '^(?:([^,]*),?){4}', 1) ProdQOH,
  regexp_extract(col_value, '^(?:([^,]*),?){5}', 1) ProdPrice,
  regexp_extract(col_value, '^(?:([^,]*),?){6}', 1) ProdNextShipDate

from product_temp;

运行上述代码后，除了prodprice列的所有值都为null之外，常规表中的所有列都是完美的。那么，如何从没有$符号的临时表中提取价格并将其加载到常规表中呢？下面是prodprice为空的当前输出。

ProdNo           ProdName         ProdMfg      ProdQOH  ProdPrice   date
P0036566 17 inch Color Monitor  ColorMeg Inc.   12      null    2013-02-20
P0036577 19 inch Color Monitor  ColorMeg Inc.   10      null    2013-02-20

下面是产品表结构

CREATE TABLE `product`(
`prodno` string,
`prodname` string,
`prodmfg` string,
`prodqoh` int,
`prodprice` string,
`prodnextshipdate` date)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.RCFileInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
LOCATION
'hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/sales_db.db/product'
TBLPROPERTIES (
'COLUMN_STATS_ACCURATE'='true',
'last_modified_by'='maria_dev',
'last_modified_time'='1488149236',
'numFiles'='1',
'numRows'='11',
'rawDataSize'='516',
'totalSize'='650',
'transient_lastDdlTime'='1488149304')

谢谢

Hive

来源：https://stackoverflow.com/questions/42463754/unable-to-extract-product-price-from-column-in-hive-table

2条答案

按热度按时间

jhiyze9q1#

insert overwrite table product    
select  val[0],val[1],val[2],val[3],val[4],val[5]           
from   (select split (col_value,',') as val from product_temp) t

演示

create table product_temp (col_value string);

insert into product_temp values

    ('P0036566,17 inch Color Monitor,ColorMeg Inc.,12,$169.00,2013-02-20')
   ,('P0036577,19 inch Color Monitor,ColorMeg Inc.,10,$319.00,2013-02-20')
   ,('P1114590,R3000 Color Laser Printer,Connex,5,$699.00,2013-01-22'    )
;

select  val[0] as ProdNo
       ,val[1] as ProdName
       ,val[2] as ProdMfg
       ,val[3] as ProdQOH
       ,val[4] as ProdPrice
       ,val[5] as ProdNextShipDate

from   (select  split (col_value,',') as val 
        from    product_temp
        ) t
;

+----------+---------------------------+---------------+---------+-----------+------------------+
|  prodno  |         prodname          |    prodmfg    | prodqoh | prodprice | prodnextshipdate |
+----------+---------------------------+---------------+---------+-----------+------------------+
| P0036566 | 17 inch Color Monitor     | ColorMeg Inc. |      12 | $169.00   | 2013-02-20       |
| P0036577 | 19 inch Color Monitor     | ColorMeg Inc. |      10 | $319.00   | 2013-02-20       |
| P1114590 | R3000 Color Laser Printer | Connex        |       5 | $699.00   | 2013-01-22       |
+----------+---------------------------+---------------+---------+-----------+------------------+

赞(0）回复(0）举报 2021-06-26

xt0899hw2#

您正在尝试插入文本，例如。 $169.00 到数字字段。
配置单元通过插入空值来处理这种不匹配。
将prodprice更改为string或删除“$”符号（如果其他货币可用，请将货币符号保存在其他列中）

赞(0）回复(0）举报 2021-06-26