Hive 去除 CSV 字段中的双引号

栏目: 服务器 · 发布时间: 5年前

内容简介:在 AWS 中,可以开启详细账单的功能。开启详细账单后,AWS 每天会多次将详细的账单数据存入到指定的 S3 bucket 中[1]。账单数据是一个 CSV 文件,示例如下:第一行是每个字段的名字,后面的行是相应的数据。

问题

在 AWS 中,可以开启详细账单的功能。开启详细账单后,AWS 每天会多次将详细的账单数据存入到指定的 S3 bucket 中[1]。

账单数据是一个 CSV 文件,示例如下:

"InvoiceID","PayerAccountId","LinkedAccountId","RecordType","ProductName","RateId","SubscriptionId","PricingPlanId","UsageType","Operation","AvailabilityZone","ReservedInstance","ItemDescription","UsageStartDate","UsageEndDate","UsageQuantity","Rate","Cost"
 "Estimated","xxxxxxxxxxxx","xxxxxxxxxxxx","LineItem","Amazon Simple Queue Service","16850885","1846142824","1292565","CNN1-Requests-Tier1","GetQueueAttributes","","N","First 1,000,000 Amazon SQS Requests per month are free","2019-01-01 00:00:00","2019-01-01 01:00:00","60.0000000000","0.0000000000","0.0000000000"
 "Estimated","xxxxxxxxxxxx","xxxxxxxxxxxx","LineItem","Amazon Simple Queue Service","16850885","1846142824","1292565","CNN1-Requests-Tier1","GetQueueUrl","","N","First 1,000,000 Amazon SQS Requests per month are free","2019-01-01 00:00:00","2019-01-01 01:00:00","180.0000000000","0.0000000000","0.0000000000"

第一行是每个字段的名字,后面的行是相应的数据。

如果想用 Hive 进行分析,按照如下方式建表,得到的每一个字段内容都会包含双引号,不方便分析。

hive > CREATE EXTERNAL TABLE IF NOT EXISTS
  aws_bill (
  InvoiceID string,
  PayerAccountId string,
  LinkedAccountId string,
  RecordType string,
  ProductName string,
  RateId string,
  SubscriptionId string,
  PricingPlanId string,
  UsageType string,
  Operation string,
  AvailabilityZone string,
  ReservedInstance string,
  ItemDescription string,
  UsageStartDate string,
  UsageEndDate string,
  UsageQuantity double,
  Rate double,
  Cost double
 )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  LOCATION 's3://feichashao-hadoop/bill/';
 
 hive> select * from aws_bill
 "Estimated","xxxxxxxxxxxx","xxxxxxxxxxxx","LineItem","Amazon Simple Queue Service","16850885","1846142824","1292565","CNN1-Requests-Tier1","GetQueueAttributes","","N","First 1,000,000 Amazon SQS Requests per month are free","2019-01-01 00:00:00","2019-01-01 01:00:00",NULL,NULL,NULL

而期望的结果是,字段中不含有双引号,如

Estimated       xxxxxxxxxxxx    xxxxxxxxxxxx    LineItem        Amazon Simple Queue Service     16850885        1846142824      1292565CNN1-Requests-Tier1      GetQueueAttributes              N       First 1,000,000 Amazon SQS Requests per month are free  2019-01-01 00:00:00     2019-01-01 01:00:00     60.0000000000   0.0000000000    0.0000000000

方法一:CSV Serde

Hive 没有原生的方法来去除字段中的双引号。不过我们可以在建表的时候,使用 CSV Serde[2]。

建表的方法如下:

Hive > CREATE EXTERNAL TABLE IF NOT EXISTS
  aws_bill_serde (
  InvoiceID string,
  PayerAccountId string,
     [...省略...] 
  Cost double)
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  LOCATION 's3://feichashao-hadoop/bill/';

这样建表,select * from aws_bill_serde 得到的结果没有双引号。

不过,用 SERDE 建表的话,无论我们指定数据类是 string 还是 double, describe table 时看到的类型全都是 string. 如果要做运算会变得不方便。

hive> describe aws_bill_serde;
 OK
 invoiceid               string                  from deserializer
 payeraccountid          string                  from deserializer
 linkedaccountid         string                  from deserializer
 recordtype              string                  from deserializer
 productname             string                  from deserializer
 rateid                  string                  from deserializer
 subscriptionid          string                  from deserializer
 pricingplanid           string                  from deserializer
 usagetype               string                  from deserializer
 operation               string                  from deserializer
 availabilityzone        string                  from deserializer
 reservedinstance        string                  from deserializer
 itemdescription         string                  from deserializer
 usagestartdate          string                  from deserializer
 usageenddate            string                  from deserializer
 usagequantity           string                  from deserializer
 cost                    string                  from deserializer

方法二:预处理 CSV 文件

既然不想要双引号,那就预先处理 csv 文件,把双引号从根本上去除。

使用 sed 命令可以实现:

$ sed 's/"//g'  old.csv > new.csv

参考文档

[1] https://docs.aws.amazon.com/awsaccountbilling/latest/aboutv2/billing-getting-started.html

[2] https://cwiki.apache.org/confluence/display/Hive/CSV+Serde


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Web 2.0 Heroes

Web 2.0 Heroes

Bradley L. Jones / Wiley / 2008-04-14 / USD 24.99

Web 2.0 may be an elusive concept, but one thing is certain: using the Web as merely a means of retrieving and displaying information is history. Today?s Web is immediate, interactive, innovative. It ......一起来看看 《Web 2.0 Heroes》 这本书的介绍吧!

MD5 加密
MD5 加密

MD5 加密工具

SHA 加密
SHA 加密

SHA 加密工具

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换