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

This article is about what data types are available in Hive. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Hive data type 1.1, basic data type

For the String type of Hive, which is equivalent to the varchar type of the database, this type is a variable string, but it cannot declare the maximum number of characters it can store, and theoretically it can store the number of characters of 2GB. 1.2. Collection data type

Hive has three complex data types: ARRAY, MAP, and STRUCT. ARRAY and MAP are similar to Array and Map in Java, while STRUCT, like Struct in C, encapsulates a collection of named fields, and complex data types allow for any level of nesting.

Case practice:

/ / 1. Suppose a table has the following row, and we use JSON format to represent its data structure. The format accessed under Hive is {"name": "kinglong", "friends": ["ZYL", "WJM"], / / list Array, "children": {/ / key value Map, "xiao chen": 2, "xiaoxiao chen": 1} "address": {/ / structure Struct "street": "wan guo cheng", "city": "chang sha", "email": "kinglong2333@163.com"} / / 2, based on the above data structure We create the corresponding table in Hive and import the data. Create a local test file test.txtkinglong,ZYL_WJM,xiao chen:2_xiaoxiao chen:1,wan guo cheng_chang sha_kinglong2333@163.comwujiaoxin,LWH_WQ,xiao wu:3_xiaoxiao wu:2,kai fu_changsha_233333@163.com Note: the relationships between elements in MAP,STRUCT and ARRAY can be represented by the same character, here with "_". / / 3. Create a test table on Hive testcreate table test (name string,friends array,children map,address struct) row format delimited fields terminated by','--specify what separates collection items terminated by'_ 'between fields-- what separates lines terminated by' _ 'between each piece of data of collection data type:'-- what separates lines terminated by'\ n 'between map data types -- what separates each piece of data (the default is the newline character) / / 4. Import text data into the test table hive (default) > load data local inpath'/ opt/module/hive/datas/test.txt' into table test / / 5. Access the data in the columns of three collections. The following are the access methods of ARRAY,MAP,STRUCT: hive (default) > select friends [1], children ['xiao chen'], address.city from testwhere name= "kinglong" OK_c0 _ C1 cityWJM 2 chang shaTime taken: 0.076 seconds, Fetched: 1 row (s) 1.3.The atomic data type of type conversion Hive can be implicitly converted, similar to the type conversion of Java. For example, if an expression uses INT type, TINYINT will automatically convert to INT type, but Hive will not do reverse conversion. For example, if an expression uses TINYINT type, INT will not automatically convert to TINYINT type. It returns an error unless the CAST operation is used. 1) implicit type conversion rules are as follows: (1) any integer type can be implicitly converted to a broader type, such as TINYINT can be converted to INT,INT can be converted to BIGINT. (2) all integer types, FLOAT and STRING types can be implicitly converted to DOUBLE. (3) TINYINT, SMALLINT and INT can all be converted into FLOAT. (4) the BOOLEAN type cannot be converted to any other type. 2) you can use the CAST operation to show that the data type conversion can be done for example, CAST ('1' AS INT) will convert the string'1' to the integer 1; if the cast fails, such as performing CAST ('X' AS INT), the expression returns a null value NULL.

2. DDL data definition 2. 1, library operation 1. Create a library: CREATE DATABASE [IF NOT EXISTS] library name # IF NOT EXISTS: create a library if it does not exist. If the inventory is present, it will not be created. # Note: if you do not add this content. Inventory will report an error when it comes to [description of COMMENT library] # description information used by comment to add library [LOCATION hdfs_path] # localtion: specify the path of the library on hdfs (if not specified, put it under the default path-the path configured in the configuration file) [WITH DBPROPERTIES (name=value,...)] # WITH DBPROPERTIES: add attributes to the library. # Note: both name and value are string II. Case: create database if not exists db2 comment 'this is db2' location' / db2.db' with dbproperties ('version'='1.0'); III. Operation on the library 1. Check out all the libraries: show databases; 2. Select the name of the library use; 3. View the information of the library (extended: you can view the attribute content of the library) desc database [extended] library name; 4. Modify the library (only attributes can be modified) alter database library name set dbproperties ('name'='value'); 5. Delete the library (cascade: you must use this field if the library is not empty to delete the library) # if exists: delete if the library exists. If the field library does not exist, the drop database [if exists] library name [cascade] 2.2 will be reported. The operation of the table: create the table CREATE [EXTERNAL] TABLE [IF NOT EXISTS] Table name # EXTERNAL: add this field. The table is an external table. If it is not added, it will be an internal table (management table) # IF NOT EXISTS: if the table does not exist, it will not be created if it exists. If the table does not add this field, an error will be reported [(field name field type [description information of COMMENT field],...)] [description information of COMMENT table] [PARTITIONED BY (field name field type [COMMENT field description information],...)] # PARTITIONED BY: specify partition field (partition table) [CLUSTERED BY (field name 1, field name 2,) # CLUSTERED BY: specify bucket field (bucket table) [SORTED BY (field name [ASC | DESC]) .)] Number of INTO buckets BUCKETS] # SORTED BY: sort by specified field when adding data (rarely used) [ROW FORMAT row_format] delimited fields terminated by' '# specify what separates the fields collection items terminated by' _'# what separates the map keys terminated by between each piece of data of the collection data type:'# what separates the lines terminated by'\ n 'between the map data type kline v # what separates each piece of data (the default is the newline character) [STORED AS storage type] # STORED AS: specifies the type of storage file (data in the table). The default is TextFile [path to the LOCATION table] [TBLPROPERTIES (name=value,...)] # to add attributes to the table. Note: both name and value are string [AS sql query statements] # create the results of the query into a table [LIKE table name] # allow users to copy existing table (table after like) structure But do not copy data II create a table case create table if not exists person (id int comment 'this is id', name string comment' this is name') comment 'this is person' row format delimited fields terminated by'\ t 'location' / person' tblproperties ('version'='1.0') -2.1Import data load data [local] inpath'/ opt/module/hive/datas/student.txt' into table person; # [local]: if local is not added, the address is HDFS. If the Local address is local-2.2 like create table if not exists person2 like person -2.3 as create table if not exists person3 as select * from person where id < 1010 -2.4 create external tables and manage tables create external table if not exists ext_tbl (id int, name string) row format delimited fields terminated by'\ t' Create table if not exists man_tbl (id int, name string) row format delimited fields terminated by'\ tdelete; Note: when an external table deletes a table, only the metadata is deleted. When the management table deletes the table, the metadata and data will be deleted together. -2.5 external tables and management tables convert the alter table table name set tblproperties ('EXTERNAL'='TRUE/FALSE') to each other; note: the content of the attribute must be capitalized. Three operation tables: 1. View table information (formatted: you can view more details) desc [formatted] table name; 2. Delete table drop table table name; 3. Modify the name of the table alter table table name rename to new table name; 4. Update column name ALTER TABLE table name CHANGE [COLUMN] old column name new column name type [COMMENT column description] [FIRST | AFTER column name] 4. Update the type of column ALTER TABLE table name CHANGE [COLUMN] column name new column type [description of COMMENT column] [FIRST | AFTER column name] Note: the modified type must conform to the implicit conversion or an error will be reported. 5. Add the column ALTER TABLE table name ADD COLUMNS (field name field type [COMMENT field description],...) 6. Replace the column ALTER TABLE table name REPLACE COLUMNS (field name field type [COMMENT field description information],...) Note: ① replaces in order if there are fewer columns to be replaced. Columns that have not been replaced are deleted directly. Be sure to pay attention to the data type when ② replaces columns. (can be implicitly converted can be replaced or reported an error) 7, clear the table (can only empty the management table-not delete the table but delete the data) truncate table table name; thank you for reading! This is the end of the article on "what data types are in Hive". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.