Shulou(Shulou.com)06/03 Report--

This article mainly explains "what functions have been added to hive". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what functions have been added to hive?"

1. Configure hive-site.xml

The CDH version first goes to the Hive configuration page

Select Advanced and find the Hive client advanced configuration code snippet configuration item for hive-site.xml

Then click Save changes and distribute the configuration.

Second, build a table

If you want to support delete and update, the output must be AcidOutputFormat and then the bucket must be split.

At present, only ORCFileformat supports AcidOutputFormat, so the parameter ('transactional' = true) must be specified when building the table.

Such as

USE test

DROP TABLE IF EXISTS S1_AC_ACTUAL_PAYDETAIL

CREATE TABLE IF NOT EXISTS S1_AC_ACTUAL_PAYDETAIL

(

INPUTDATE STRING

SERIALNO STRING

PAYDATE STRING

ACTUALPAYDATE STRING

CITY STRING

PRODUCTID STRING

SUBPRODUCTTYPE STRING

ISP2P STRING

ISCANCEL STRING

CDATE STRING

PAYTYPE STRING

ASSETSOWNER STRING

ASSETSOUTDATE STRING

CPD DOUBLE

PAYPRINCIPALAMT BIGINT

PAYINTEAMT BIGINT

A2 BIGINT

A7 BIGINT

A9 BIGINT

A10 BIGINT

A11 BIGINT

A12 BIGINT

A17 BIGINT

A18 BIGINT

PAYAMT BIGINT

LOANNO STRING

CREATEDATE STRING

CUSTOMERID STRING

Etl_in_dt string

)

CLUSTERED BY (SERIALNO)-- dividing buckets according to a field

INTO 7 BUCKETS-how many buckets are divided

ROW FORMAT DELIMITED

FIELDS TERMINATED BY','

LINES TERMINATED BY'\ n'

STORED AS ORC

LOCATION'/ user/hive/test/S1_AC_ACTUAL_PAYDETAIL'

TBLPROPERTIES ('transactional'='true');-- increase the amount of description information, such as the last modification information, the last modifier.

Note: because cdh automatically creates the COMPACTION_QUEUE table in the metadata, the problem mentioned in the blog does not exist

III. Operation

Execution

Update test.S1_AC_ACTUAL_PAYDETAIL set city='023' where SERIALNO = '20688947002'

It takes more than 2 seconds to operate 100 pieces of data on average, and the execution takes about 1 second. It's relatively acceptable.

Delete from test.S1_AC_ACTUAL_PAYDETAIL where SERIALNO = '20688947002'

IV. Summary

1. Hive can modify and delete data by modifying parameters, but it is not as fast as traditional relational databases.

2. Through the characteristic that each task of ORC outputs only a single file and its own index, as well as the bucket operation of the data, the modified data can be locked in a very small file block, so it is relatively convenient to modify the file. Therefore, the data bucket operation is very important, usually some form information will be deleted and modified according to the specific form id, so it is recommended to use the form ID as the bucket field.

3. Frequent update and delete operations have gone against the original intention of hive. It is best to use incremental addition until it is a last resort.

At this point, I believe you have a deeper understanding of "what functions have been added to hive". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.