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

Thursday, 2019-2-28

There is a problem of garbled Chinese characters in hive creation table in cdh.

0: jdbc:hive2://192.168.0.141:10000 > create table movie (. . . . . . . . . . . . . . . . . . > userID int comment 'user ID',. . . . . . . . . . . . . . . . . . > movieID int comment 'movie ID',. . . . . . . . . . . . . . . . . . > rating int comment 'Movie score'. . . . . . . . . . . . . . . . . . > timestamped bigint comment 'scoring timestamp',. . . . . . . . . . . . . . . . . . > movieName string comment 'movie name'. . . . . . . . . . . . . . . . . . > movieType string comment 'movie type'. . . . . . . . . . . . . . . . . . > sex string comment 'gender'. . . . . . . . . . . . . . . . . . > age int comment 'age'. . . . . . . . . . . . . . . . . . > occupation string comment 'occupation'. . . . . . . . . . . . . . . . . . > zipcode string comment 'zip code'. . . . . . . . . . . . . . . . . . >) comment 'three forms of film review in one'. . . . . . . . . . . . . . . . . . > row format delimited fields terminated by "," . . . . . . . . . . . . . . . . . >

0: jdbc:hive2://192.168.0.141:10000 > desc movie

INFO: Compiling command (queryId=hive_20190228181919_b6eb4f8a-7d17-4437-b089-750bf2e81d0f): desc movie

INFO: Semantic Analysis Completed

INFO: Returning Hive schema: Schema (fieldSchemas: [field schema (name:col_name, type:string, comment:from deserializer), FieldSchema (name:data_type, type:string, comment:from deserializer), FieldSchema (name:comment, type:string, comment:from deserializer)], properties:null)

INFO: Completed compiling command (queryId=hive_20190228181919_b6eb4f8a-7d17-4437-b089-750bf2e81d0f); Time taken: 0.036 seconds

INFO: Executing command (queryId=hive_20190228181919_b6eb4f8a-7d17-4437-b089-750bf2e81d0f): desc movie

INFO: Starting task [Stage-0:DDL] in serial mode

INFO: Completed executing command (queryId=hive_20190228181919_b6eb4f8a-7d17-4437-b089-750bf2e81d0f); Time taken: 0.016 seconds

INFO: OK

+-+

| | col_name | data_type | comment | |

+-+

| | userid | int |? ID | |

| | movieid | int |? ID | |

| | rating | int |? |

| | timestamped | bigint |? | |

| | moviename | string |? |

| | movietype | string |? |

| | sex | string |? |

| | age | int |? |

| | occupation | string |? |

| | zipcode | string |? |

+-+

Enter mysql

[root@NewCDH-0--141] # mysql-uroot-p

Enter password:

Mysql > show create database hive

+-+

| | Database | Create Database |

+-+

| | hive | CREATE DATABASE hive /! 40100 DEFAULT CHARACTER SET utf8 / | |

+-+

1 row in set (0.02 sec)

Execute the following command directly

Mysql > use hive;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-Amysql > alter table COLUMNS_V2 modify column COMMENT varchar character set utf8;Query OK, 57 rows affected (0.20 sec) Records: 57 Duplicates: 0 Warnings: 0mysql > alter table TABLE_PARAMS modify column PARAM_VALUE varchar (4000) character set utf8;Query OK, 58 rows affected (0.03 sec) Records: 58 Duplicates: 0 Warnings: 0mysql > alter table PARTITION_PARAMS modify column PARAM_VALUE varchar (4000) character set utf8 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table PARTITION_KEYS modify column PKEY_COMMENT varchar (4000) character set utf8;Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table INDEX_PARAMS modify column PARAM_VALUE varchar (4000) character set utf8;Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0

Then you need to recreate the table, and the previous table is still garbled.

Create table movie02 (userID int comment 'user ID',movieID int comment' Movie ID',rating int comment 'Movie rating', timestamped bigint comment 'rating timestamp', movieName string comment 'Movie name', movieType string comment 'Movie Type', sex string comment 'gender', age int comment 'Age', occupation string comment 'occupation', zipcode string comment 'Postal Code') comment 'Film Review three forms in one' row format delimited fields terminated by ","

0: jdbc:hive2://192.168.0.141:10000 > desc movie02

+-+

| | col_name | data_type | comment | |

+-+

| | userid | int | user ID |

| | movieid | int | movie ID |

| | rating | int | Movie rating |

| | timestamped | bigint | scoring timestamp |

| | moviename | string | Movie name |

| movietype | string | Movie type |

| | sex | string | gender | |

| | age | int | Age | |

| | occupation | string | occupation |

| | zipcode | string | ZIP code |

+-+

0: jdbc:hive2://192.168.0.141:10000 > show create table movie02

+-+ +

| | createtab_stmt |

+-+ +

| | CREATE TABLE movie02 (| |

| | userid int COMMENT 'user ID', |

| | movieid int COMMENT 'Movie ID', |

| | rating int COMMENT 'movie rating', |

| | timestamped bigint COMMENT 'scoring timestamp', |

| | moviename string COMMENT 'Movie name', |

| | movietype string COMMENT "Movie Type", |

| | sex string COMMENT 'gender', |

| | age int COMMENT 'age', |

| | occupation string COMMENT 'occupation', |

| | zipcode string COMMENT 'ZIP code') |

| | COMMENT 'Film Review with three tables in one' |

| | ROW FORMAT SERDE |

| | 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' |

| | WITH SERDEPROPERTIES (| |

| | 'field.delim'=',', |

| | 'serialization.format'=',') |

| | STORED AS INPUTFORMAT |

| | 'org.apache.hadoop.mapred.TextInputFormat' |

| | OUTPUTFORMAT |

| | 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |

| | LOCATION |

| | 'hdfs://nameservice1/user/hive/warehouse/movie02' |

| | TBLPROPERTIES (| |

| | 'transient_lastDdlTime'='1551350122') |

+-+ +

25 rows selected (0.167 seconds)

The garbled problem has been solved.

Reference link

Https://www.cnblogs.com/qingyunzong/p/8724155.html

Cdh hive Chinese comment garbled solution (simple steps): https://blog.csdn.net/lingbo229/article/details/81324624

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.