Keyword in Hive

Keywords are characters that are required in any language. These characters have special meanings and cannot be used directly because the compiler has special treatment for keywords.

Hive has reserved keywords that cannot be used as identifiers when executing certain statements, such as table names or field names for table sentences

The most common user keyword

Table alias: table alias: table alias: table alias: table alias: table alias: table alias

hive> select * from user_log user;

NoViableAltException(311@ [157:5: ( ( Identifier LPAREN )=> partitionedTableFunction | tableSource | subQuerySource | virtualTableSource )])
	at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
	at org.antlr.runtime.DFA.predict(DFA.java:116)
	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource0(HiveParser_FromClauseParser.java:2901)
	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:2839)
	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:1410)
	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1300)
FAILED: ParseException line 1:23 cannot recognize input near 'user_log' 'user' '<EOF>' in from source 0
Copy the code

The user alias for the field is also an error

hive> select userid user from user_log ;
NoViableAltException(311@ [123:1: selectItem : ( ( tableAllColumns )=> tableAllColumns -> ^( TOK_SELEXPR tableAllColumns ) | ( expression ( ( ( KW_AS )? identifier ) | ( KW_AS LPAREN identifier ( COMMA identifier )* RPAREN ) )? ) -> ^( TOK_SELEXPR expression ( identifier )*)); ] )at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
	at org.antlr.runtime.DFA.predict(DFA.java:116)
	at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectItem(HiveParser_SelectClauseParser.java:2244)
	at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectList(HiveParser_SelectClauseParser.java:1144)
	at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectClause(HiveParser_SelectClauseParser.java:939)
	at org.apache.hadoop.hive.ql.parse.HiveParser.selectClause(HiveParser.java:39575)
	at org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:34882)
	at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:34803)
	at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:33992)
	at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:33880)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:313)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:227)
FAILED: ParseException line 1:14 cannot recognize input near 'userid' 'user' 'from' in selection target
Copy the code

If you really want to alias keywords, you can use backquotation marks (” dot “) around the key below Esc, like this:

-- the user keyword is used to alias the table
select * from user_log `user`;

-- the user keyword alias for the column
select userid `user` from user_log ;
Copy the code

Shielding key word recognition rules in the Hive 2.1.0 and earlier versions can also by setting the set Hive. Support. Sql11. Reserved. Keywords = false; Disable reserved word verification (The latest Hive version does not support this configuration item and is invalid). Or set it in the hive-site. XML file

<property>
  <name>hive.support.sql11.reserved.keywords</name>
  <value>false</value>
</property>
Copy the code

Hive versions later than 2.1.0 do not support this setting and can only use backquotes

And like the as

The like keyword in Hive allows you to copy the structure of an existing table.

create  table  person1  likeThe person;Copy the code

As in Hive can create a table whose data content is the result of the query statement. If the result of the query statement is not empty, the table contains data after the table is created.

create  table  person2  as  select * from person where sex ="Male";Copy the code

The local keyword in Hive

The local keyword in Hive indicates that files are loaded to Hive from the local machine (the machine on which Hive is running or the Hive service is enabled). The Hive service client (the machine on which commands are written) is not the local machine.

Keyword list in Hive

Version Non-reserved Keywords Reserved Keywords
Hive – 1.2.0 ADD, ADMIN, AFTER, ANALYZE, ARCHIVE, ASC, BEFORE, BUCKET, BUCKETS, CASCADE, CHANGE, CLUSTER, CLUSTERED, CLUSTERSTATUS, COLLECTION, COLUMNS, COMMENT, COMPACT, COMPACTIONS, COMPUTE, CONCATENATE, CONTINUE, DATA, DATABASES, DATETIME, DAY, DBPROPERTIES, DEFERRED, DEFINED, DELIMITED, DEPENDENCY, DESC, DIRECTORIES, DIRECTORY, DISABLE, DISTRIBUTE, ELEM_TYPE, ENABLE, ESCAPED, EXCLUSIVE, EXPLAIN, EXPORT, FIELDS, FILE, FILEFORMAT, FIRST, FORMAT, FORMATTED, FUNCTIONS, HOLD_DDLTIME, HOUR, IDXPROPERTIES, IGNORE, INDEX, INDEXES, INPATH, INPUTDRIVER, INPUTFORMAT, ITEMS, JAR, KEYS, KEY_TYPE, LIMIT, LINES, LOAD, LOCATION, LOCK, LOCKS, LOGICAL, LONG, MAPJOIN, MATERIALIZED, METADATA, MINUS, MINUTE, MONTH, MSCK, NOSCAN, NO_DROP, OFFLINE, OPTION, OUTPUTDRIVER, OUTPUTFORMAT, OVERWRITE, OWNER, PARTITIONED, PARTITIONS, PLUS, PRETTY, PRINCIPALS, PROTECTION, PURGE, READ, READONLY, REBUILD, RECORDREADER, RECORDWRITER, REGEXP, RELOAD, RENAME, REPAIR, REPLACE, REPLICATION, RESTRICT, REWRITE, RLIKE, ROLE, ROLES, SCHEMA, SCHEMAS, SECOND, SEMI, SERDE, SERDEPROPERTIES, SERVER, SETS, SHARED, SHOW, SHOW_DATABASE, SKEWED, SORT, SORTED, SSL, STATISTICS, STORED, STREAMTABLE, STRING, STRUCT, TABLES, TBLPROPERTIES, TEMPORARY, TERMINATED, TINYINT, TOUCH, TRANSACTIONS, UNARCHIVE, UNDO, UNIONTYPE, UNLOCK, UNSET, UNSIGNED, URI, USE, UTC, UTCTIMESTAMP, VALUE_TYPE, VIEW, WHILE, YEAR ALL, ALTER, AND, ARRAY, AS, AUTHORIZATION, BETWEEN, BIGINT, BINARY, BOOLEAN, BOTH, BY, CASE, CAST, CHAR, COLUMN, CONF, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_TIMESTAMP, CURSOR, DATABASE, DATE, DECIMAL, DELETE, DESCRIBE, DISTINCT, DOUBLE, DROP, ELSE, END, EXCHANGE, EXISTS, EXTENDED, EXTERNAL, FALSE, FETCH, FLOAT, FOLLOWING, FOR, FROM, FULL, FUNCTION, GRANT, GROUP, GROUPING, HAVING, IF, IMPORT, IN, INNER, INSERT, INT, INTERSECT, INTERVAL, INTO, IS, JOIN, LATERAL, LEFT, LESS, LIKE, LOCAL, MACRO, MAP, MORE, NONE, NOT, NULL, OF, ON, OR, ORDER, OUT, OUTER, OVER, PARTIALSCAN, PARTITION, PERCENT, PRECEDING, PRESERVE, PROCEDURE, RANGE, READS, REDUCE, REVOKE, RIGHT, ROLLUP, ROW, ROWS, SELECT, SET, SMALLINT, TABLE, TABLESAMPLE, THEN, TIMESTAMP, TO, TRANSFORM, TRIGGER, TRUE, TRUNCATE, UNBOUNDED, UNION, UNIQUEJOIN, UPDATE, USER, USING, UTC_TMESTAMP, VALUES, VARCHAR, WHEN, WHERE, WINDOW, WITH
Hive – 2.0.0 removed: REGEXP, RLIKE

added: AUTOCOMMIT, ISOLATION, LEVEL, OFFSET, SNAPSHOT, TRANSACTION, WORK, WRITE		 added: COMMIT, ONLY, REGEXP, RLIKE, ROLLBACK, START
Hive – 2.1.0 added: ABORT, KEY, LAST, NORELY, NOVALIDATE, NULLS, RELY, VALIDATE added: CACHE, CONSTRAINT, FOREIGN, PRIMARY, REFERENCES
Hive – 2.2.0 added: DETAIL, DOW, EXPRESSION, OPERATOR, QUARTER, SUMMARY, VECTORIZATION, WEEK, YEARS, MONTHS, WEEKS, DAYS, HOURS, MINUTES, SECONDS added: DAYOFWEEK, EXTRACT, FLOOR, INTEGER, PRECISION, VIEWS
Hive 3.0.0 added: TIMESTAMPTZ, ZONE added: TIME, NUMERIC, SYNC

Reserved keywords are permitted as identifiers if you quote them as described in Supporting Quoted Identifiers in Column Names (version 0.13.0 and later, see HIVE-6013). Most of the keywords are reserved through HIVE-6617 in order to reduce the ambiguity in grammar (version 1.2.0 and later). There are two ways if the user still would like to use those reserved keywords as identifiers: (1) the use of quoted identifiers, (2) set hive. Support. Sql11. Reserved. Keywords = false. (version 2.1.0 and earlier)