Hive_SQL（二）

hive> create table pi_cust_item_recommend2(
    > cust_id string,
    >  item_id bigint,
    >  advise_level double
    > )
    > partitioned by(
    > ymday string)
    > ;
OK
Time taken: 0.204 seconds
hive> describe pi_cust_item_recommend2;
OK
cust_id                 string
item_id                 bigint
advise_level            double
ymday                   string

# Partition Information
# col_name              data_type               comment

ymday                   string
Time taken: 0.065 seconds, Fetched: 9 row(s)
hive> select * from pi_cust_item_recommend2 limit 31;
OK
110101100985    90190202        25.33671962331747       20170830
110101100985    34030326        23.906456902069216      20170830
110101100985    90020726        16.010945324507635      20170830
110101100985    90190101        15.628847628582498      20170830
110101100985    90020727        15.442605374580097      20170830
110101100985    90020214        13.276358249567217      20170830
110101100985    90160248        12.218660323168212      20170830
110101100985    90020222        11.55113640113565       20170830
110101100985    90160257        11.001857833289318      20170830
110101100985    53020411        10.808402849652841      20170830
110101100985    90160255        10.55634487493214       20170830
110101100985    43010101        10.293531517670374      20170830
110101100985    53020502        9.967549321098497       20170830
110101100985    34020101        9.52875123200268        20170830
110101100985    34030228        9.479434291239455       20170830
110101100985    37020308        9.472155882738065       20170830
110101100985    90020927        9.112810136181182       20170830
110101100985    90020934        9.112810136181182       20170830
110101100985    90021202        9.112810136181182       20170830
110101100985    33010104        9.041770412446649       20170830
110101100985    90021408        8.76375384049508        20170830
110101100985    41020211        8.753379761624661       20170830
110101100985    53020413        8.720947985984836       20170830
110101100985    32010101        8.66504292797535        20170830
110101100985    13070505        8.536145306189216       20170830
110101100985    53090115        8.490782608501519       20170830
110101100985    22240103        8.456414691761115       20170830
110101100985    53010210        8.330416055770293       20170830
110101100985    12010504        8.319379734896234       20170830
110101100985    35260104        8.090941846795186       20170830
110101101012    34030316        45.461241571600425      20170830
Time taken: 0.127 seconds, Fetched: 31 row(s)
hive> select count(distinct cust_id) from pi_cust_item_recommend2 limit 3;
Query ID = hive_20170831001737_95d891d6-319e-483e-897f-8287b5452dfd
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.


Status: Running (Executing on YARN cluster with App id application_1503306179155_0057)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      4          4        0        0       0       0
Reducer 2 ......   SUCCEEDED      1          1        0        0       0       0
Reducer 3 ......   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 03/03  [==========================>>] 100%  ELAPSED TIME: 8.81 s
--------------------------------------------------------------------------------
OK
33695
Time taken: 15.046 seconds, Fetched: 1 row(s)
hive> select * from pi_cust_item_recommend limit 3;
OK
Time taken: 0.13 seconds
hive> describe pi_cust_item_recommend;
OK
cust_id                 string
item_id                 bigint
advise_level            decimal(10,0)
date                    int
cust_code               varchar(30)
pack_bar                varchar(30)
ymday                   string

# Partition Information
# col_name              data_type               comment

ymday                   string
Time taken: 0.066 seconds, Fetched: 12 row(s)
hive> ALTER TABLE pi_cust_item_recommend REPLACE COLUMNS ( cust_code string,pack_bar string )
    > ;
OK
Time taken: 0.087 seconds
hive> describe pi_cust_item_recommend;
OK
cust_code               string
pack_bar                string
ymday                   string

# Partition Information
# col_name              data_type               comment

ymday                   string
Time taken: 0.081 seconds, Fetched: 8 row(s)

hive> select * from pi_cust_item_recommend2 limit 3;
OK
110101100985    90190202        25.33671962331747       20170830
110101100985    34030326        23.906456902069216      20170830
110101100985    90020726        16.010945324507635      20170830
Time taken: 0.308 seconds, Fetched: 3 row(s)

hive&g