Hive QL (HQL) Concise Guide zz

1. Basic data type

Tinyint, smallint, int, bigint, float, double, boolean: true/false, string

2. Basic operators and functions

A IS NULL
A is not null non-empty
A like B fuzzy match
A rlike B Regular Expression match
A regexp B Regular Expression match

3. type conversion
Cast (expr as <type>)
For example:
Cast ('1' as BIGINT) converts string '1' to bigint type

4. Date Functions

Return Value Type Name Description
String from_unixtime (int unixtime) converts a timestamp (unix epoch seconds) to a date and time string, for example, from_unixtime (0) = "00:00:00"
Bigint unix_timestamp () to obtain the current Timestamp
Bigint unix_timestamp (string date) obtains the timestamp represented by date.
Bigint to_date (string timestamp) returns a date string, for example, to_date ("00:00:00") = "1970-01-01"
String year (string date) returns the year, for example, year ("00:00:00") = 1970, year ("") = 1970
Int month (string date)
Int Day (string date) dayofmonth (date)
Int hour (string date)
Int minute (string date)
Int second (string date)
Int weekofyear (string date)
Int datediff (string enddate, string startdate) returns the number of days between enddate and startdate, for example, datediff ('2017-03-01 ', '2017-02-27') = 2
Int date_add (string startdate, int days) plus days to startdate: date_add ('2017-12-31 ', 1) = '2017-01-01'
Int date_sub (string startdate, int days) minus days to startdate: date_sub ('2017-12-31 ', 1) = '2017-12-30'

5. Conditional Functions

Return Value Type Name Description
-If (boolean testCondition, T valueTrue, T valueFalseOrNull): if testCondition is true, valueTrue is returned. if testCondition is false or NULL, valueFalseOrNull is returned.
-COALESCE (T v1, T v2,...) returns the first non-empty element in the list. If the list element is empty, NULL is returned.
-CASE a WHEN B THEN c [WHEN d THEN e] * [ELSE f] END a = B, return c; a = d, return e; otherwise, return f
-Case when a THEN B [WHEN c THEN d] * [ELSE e] END a is true, return B; c is true, return d; otherwise e
For example:
(
Case
When category = '000000' then reserve_price> cast (1512 as double)
When category = '000000' then reserve_price> cast (1101 as double)
Else reserve_price> cast (10 as double)
End
)

6. common string functions
Return Value Type Name Description
Int length (string A) returns the string length.
String reverse (string A) reverse string
String concat (string A, string B...) merges strings, such as concat ('foo', 'bar') = 'foobar '. Note that this function can accept any number of parameters.
String substr (string A, int start) substring (string A, int start) returns A substring, for example, substr ('foobar', 4) = 'bar'. For details, see [4].
String substr (string A, int start, int len) substring (string A, int start, int len) returns A substring of limited length, such as substr ('foobar', 4, 1) = 'B'. For details, refer to [5].
String upper (string A) ucase (string A) is converted to uppercase
String lower (string A) lcase (string A) to lowercase
String trim (string)
String ltrim (string)
String rtrim (string)
String regexp_extract (string subject, string pattern, int intex) returns the substring extracted using a regular expression.
For example, regexp_extract ('foothebar', 'foo (.*?) (Bar) ', 2) = 'bar '. Note:
'\ S' indicates the character' s ',' \ s' is required for blank characters, and so on.
String space (int n) returns a string containing n Spaces
String repeat (string str, int n) repeats str string n times
String ascii (string str) returns the ascii code of the first character in str.
String lpad (string str, int len, string pad) Fill str on the left end to the length of len. Pad specifies the completed string.
String rpad (string str, int len, string pad) Fill str on the right end to the length of len. Pad specifies the completed string.
Array split (string STR, string Pat) returns the list of STR strings separated by Pat as a regular expression. For example, split ('foobar', 'O') [2] = 'bar '.

7. Create a table

Create Table if not exists table_name
(

-- Field def

)

Partitioned by (PT string)
Row format delimited
Fields terminated by '\ t'
Stored as textfile
Location '...';

Note: if it is not the External table, the files on HDFS will be deleted when you drop the table.

8. Create an External table

Create external table dm_all_cpv_assoc (
-- Field def
)
Partitioned by (PT string)
Row format delimited
Fields terminated by '\ 1' field separator
Lines terminated by '\ 2' line Separator
Stored as textfile
Location '...';

Note: When you delete an External table, the associated files on HDFS are not deleted.

9. Add partitions

Alter table table_name add partition (Dt = '2017-08-08 ', Country = 'us ')
Location '/path/to/US/part080808' partition (Dt = '2017-08-09 ', Country = 'use ')
Location '/path/to/US/part080809 ';

10. delete partitions

Alter table table_name drop partition (Dt = '2017-08-08 ', Country = 'us ');

11. Import Data

A. insert overwrite table table_name partition (pt = '000000 ')
Select... from...

B. LOAD DATA LOCAL INPATH 'test. dat 'OVERWRITE INTO table yahoo_music partition (pt = xxx );

12. query data

SELECT, JOIN, LIMIT

13. Add a UDF

Add jar/home/hive/jar/my_udf.jar;
Create temporary function sys_date as 'com. taobao. hive. udf. udfdatesysdate ';

14. Set the number of reducers

Maximum number of reducers allowed: set hive.exe c. reducers. max = 15;

Set a fixed number of Reducers: set mapred. reduce. tasks = 15;