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

This article mainly introduces "how to use the proxy key in Hive3". In the daily operation, I believe that many people have doubts about how to use the proxy key in Hive3. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to use the proxy key in Hive3". Next, please follow the editor to study!

You can use the built-in SURROGATE_KEY user-defined function (UDF) to automatically generate digital ID for rows when you enter data into a table. The generated surrogate key can replace multiple compound keys. Hive supports surrogate keys only on ACID tables, as described in the following table type matrix: table type ACID surrogate key file format insert update / delete managed: CRUD transactionality is ORC is managed: only plug-in transactions are any are not managed: temporarily there is no no external no tables that you want to join with surrogate keys cannot have column types that need to be converted. These data types must be native, such as INT or STRING. It is faster to join with the generated key than with a string. Using the generated key does not force data into a single node by line number. You can generate keys as abstractions of natural keys. Surrogate keys have an advantage over UUID, which is slower and probabilistic. SURROGATE_KEY UDF generates a unique ID for each row you insert into the table. It generates keys based on the execution environment in a distributed system, including many factors, such as the internal data structure, the state of the table, and the final transaction ID. Surrogate key generation does not require any coordination between computational tasks. UDF takes no parameters or two parameters:

Write Id bit

Task ID bit

1. Create a student table in the default ORC format with the ACID attribute. CREATE TABLE students (row_id INT, name VARCHAR (64), dorm INT); Insert data into the table. For example: INSERT INTO TABLE students VALUES (1, 'fred flintstone', 100), (2,' barney rubble', 200); 3. Use SURROGATE_KEY UDF to create a version of the student table. CREATE TABLE students_v2 (`ID`BIGINT DEFAULT SURROGATE_KEY (), row_id INT, name VARCHAR (64), dorm INT, PRIMARY KEY (ID) DISABLE NOVALIDATE); 4. Insert data, which automatically generates a surrogate key for the primary key. INSERT INTO students_v2 (row_id, name, dorm) SELECT * FROM students;5. Take a look at the proxy key. SELECT * FROM students_v2 +-+ | students_v2.id | students_v2.row_id | students_v2.name | students_v2.dorm | +- -+ | 1099511627776 | 1 | fred flintstone | 100 | | 1099511627777 | 2 | barney rubble | 200 | +-+ 6. Add a surrogate key as a foreign key to another table, such as the student_grades table, to speed up subsequent joins of the table. ALTER TABLE student_grades ADD COLUMNS (gen_id BIGINT); MERGE INTO student_grades g USING students_v2 s ON g.row_id = s.row_idWHEN MATCHED THEN UPDATE SET gen_id = s.id; now you can implement a fast join on the surrogate key.

At this point, the study on "how to use the proxy key in Hive3" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.