>>
>>set schema hive.hive;

--- SQL operation complete.
>>--cqd attempt_esp_parallelism 'off';
>>--cqd hive_max_esps  '1';
>>--cqd PARALLEL_NUM_ESPS '1';
>>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '25' ;

--- SQL operation complete.
>>cqd mode_seahive 'ON';

--- SQL operation complete.
>>cqd auto_query_retry_warnings 'ON';

--- SQL operation complete.
>>
>>prepare s from insert into hive.ins_customer select * from hive.customer;

--- SQL command prepared.
>>execute s;

--- 100000 row(s) inserted.
>>select count(*) from hive.customer;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>select count(*) from hive.ins_customer;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>--select from hive table where row delimiter was explicettely specified
>>select [first 20] * from hive.ins_customer order by c_customer_sk;

C_CUSTOMER_SK  C_CUSTOMER_ID              C_CURRENT_CDEMO_SK  C_CURRENT_HDEMO_SK  C_CURRENT_ADDR_SK  C_FIRST_SHIPTO_DATE_SK  C_FIRST_SALES_DATE_SK  C_SALUTATION               C_FIRST_NAME               C_LAST_NAME                C_PREFERRED_CUST_FLAG      C_BIRTH_DAY  C_BIRTH_MONTH  C_BIRTH_YEAR  C_BIRTH_COUNTRY            C_LOGIN                    C_EMAIL_ADDRESS            C_LAST_REVIEW_DATE
-------------  -------------------------  ------------------  ------------------  -----------------  ----------------------  ---------------------  -------------------------  -------------------------  -------------------------  -------------------------  -----------  -------------  ------------  -------------------------  -------------------------  -------------------------  -------------------------

            1  AAAAAAAABAAAAAAA                       980124                7135              32946                 2452238                2452208  Mr.                        Javier                     Lewis                      Y                                    9             12          1936  CHILE                                                 Javier.Lewis@VFAxlnZEvOx.  2452508                  
            2  AAAAAAAACAAAAAAA                       819667                1461              31655                 2452318                2452288  Dr.                        Amy                        Moses                      Y                                    9              4          1966  TOGO                                                  Amy.Moses@Ovk9KjHH.com     2452318                  
            3  AAAAAAAADAAAAAAA                      1473522                6247              48572                 2449130                2449100  Miss                       Latisha                    Hamilton                   N                                   18              9          1979  NIUE                                                  Latisha.Hamilton@V.com     2452313                  
            4  AAAAAAAAEAAAAAAA                      1703214                3986              39558                 2450030                2450000  Dr.                        Michael                    White                      N                                    7              6          1983  MEXICO                                                Michael.White@i.org        2452361                  
            5  AAAAAAAAFAAAAAAA                       953372                4470              36368                 2449438                2449408  Sir                        Robert                     Moran                      N                                    8              5          1956  FIJI                                                  Robert.Moran@Hh.edu        2452469                  
            6  AAAAAAAAGAAAAAAA                       213219                6374              27082                 2451883                2451853  Ms.                        Brunilda                   Sharp                      N                                    4             12          1925  SURINAME                                              Brunilda.Sharp@T3pylZEUQj  2452430                  
            7  AAAAAAAAHAAAAAAA                        68377                3219              44814                 2451438                2451408  Ms.                        Fonda                      Wiles                      Y                                   24              4          1985  GAMBIA                                                Fonda.Wiles@S9KnyEtz9hv.o  2452360                  
            8  AAAAAAAAIAAAAAAA                      1215897                2471              16598                 2449406                2449376  Sir                        Ollie                      Shipman                    N                                   26             12          1938  KOREA, REPUBLIC OF                                    Ollie.Shipman@be.org       2452334                  
            9  AAAAAAAAJAAAAAAA                      1168667                1404              49388                 2452275                2452245  Sir                        Karl                       Gilbert                    N                                   26             10          1966  MONTSERRAT                                            Karl.Gilbert@Crg5KyP2IxX9  2452454                  
           10  AAAAAAAAKAAAAAAA                      1207553                5143              19580                 2451353                2451323  Ms.                        Albert                     Brunson                    N                                   15             10          1973  JORDAN                                                Albert.Brunson@62.com      2452641                  
           11  AAAAAAAALAAAAAAA                      1114415                6807              47999                 2452288                2452258  Ms.                        Betty                      Williams                   N                                   18             12          1963  BURKINA FASO                                          Betty.Williams@xRtDqM1eLB  2452398                  
           12  AAAAAAAAMAAAAAAA                       502141                6577              47366                 2451039                2451009  Ms.                        Margaret                   Farias                     N                                    2              6          1956  TURKMENISTAN                                          Margaret.Farias@cb.edu     2452634                  
           13  AAAAAAAANAAAAAAA                      1128748                2777              14006                 2449658                2449628  Mrs.                       Rosalinda                  Grimes                     N                                    1              3          1970  UKRAINE                                               Rosalinda.Grimes@tC8pcU7L  2452616                  
           14  AAAAAAAAOAAAAAAA                       929344                 892               6440                 2450318                2450288  Mr.                        Jack                       Wilcox                     N                                   30              3          1937  SLOVENIA                                              Jack.Wilcox@Y3Etqyv3.org   2452641                  
           15  AAAAAAAAPAAAAAAA                            ?                 134              30469                       ?                2449010  Ms.                        Tonya                                                                                      ?             12          1969                                                                                   2452376                  
           16  AAAAAAAAABAAAAAA                      1196373                3014              29302                 2451346                2451316  Dr.                        Margie                     Browning                   N                                   24             12          1933  PHILIPPINES                                           Margie.Browning@LM674NrE2  2452573                  
           17  AAAAAAAABBAAAAAA                       707524                3876               2228                 2451068                2451038  Dr.                        Lee                        Stovall                    N                                   23             12          1972  PHILIPPINES                                           Lee.Stovall@fqKC83UU0f.or  2452454                  
           18  AAAAAAAACBAAAAAA                      1361151                6580              18456                 2450041                2450011  Sir                        Brad                       Lynch                      Y                                    1              9          1950  URUGUAY                                               Brad.Lynch@nAbai.edu       2452549                  
           19  AAAAAAAADBAAAAAA                      1161742                4238              45581                 2449580                2449550  Dr.                        Andre                      Moore                      N                                   20             10          1978  NICARAGUA                                             Andre.Moore@cTZLGYi1ZJi.o  2452576                  
           20  AAAAAAAAEBAAAAAA                      1185612                  89              38966                 2450965                2450935  Mr.                        Stanton                    Dallas                     Y                                   17              5          1976  SWITZERLAND                                           Stanton.Dallas@DBXgl18FGo  2452334                  

--- 20 row(s) selected.
>>
>>prepare s from insert into hive.ins_promotion select * from hive.promotion;

--- SQL command prepared.
>>execute s;

--- 300 row(s) inserted.
>>select count(*) from hive.promotion;

(EXPR)              
--------------------

                 300

--- 1 row(s) selected.
>>select count(*) from hive.ins_promotion;

(EXPR)              
--------------------

                 300

--- 1 row(s) selected.
>>--select from hive table where row delimiter was explicettely specified
>>select [first 20] * from hive.ins_promotion order by p_promo_sk ;

P_PROMO_SK   P_PROMO_ID                 P_START_DATE_SK  P_END_DATE_SK  P_ITEM_SK    P_COST           P_RESPONSE_TARGET  P_PROMO_NAME               P_CHANNEL_DMAIL            P_CHANNEL_EMAIL            P_CHANNEL_CATALOG          P_CHANNEL_TV               P_CHANNEL_RADIO            P_CHANNEL_PRESS            P_CHANNEL_EVENT            P_CHANNEL_DEMO             P_CHANNEL_DETAILS          P_PURPOSE                  P_DISCOUNT_ACTIVE
-----------  -------------------------  ---------------  -------------  -----------  ---------------  -----------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------  -------------------------

          1  AAAAAAAABAAAAAAA                   2450164        2450185        10022   1.0000000E+003                  1  ought                      Y                          N                          N                          N                          N                          N                          N                          N                          Men will not say merely.   Unknown                    N                        
          2  AAAAAAAACAAAAAAA                   2450118        2450150         2410   1.0000000E+003                  1  able                       Y                          N                          N                          N                          N                          N                          N                          N                          So willing buildings coul  Unknown                    N                        
          3  AAAAAAAADAAAAAAA                   2450675        2450712        10843   1.0000000E+003                  1  pri                        Y                          N                          N                          N                          N                          N                          N                          N                          Companies shall not pr     Unknown                    N                        
          4  AAAAAAAAEAAAAAAA                   2450633        2450646         9794   1.0000000E+003                  1  ese                        N                          N                          N                          N                          N                          N                          N                          N                          High, good shoulders can   Unknown                    N                        
          5  AAAAAAAAFAAAAAAA                   2450347        2450357         6655   1.0000000E+003                  1  anti                       N                          N                          N                          N                          N                          N                          N                          N                          Huge, competent structure  Unknown                    N                        
          6  AAAAAAAAGAAAAAAA                   2450516        2450561         9464   1.0000000E+003                  1  cally                      N                          N                          N                          N                          N                          N                          N                          N                          Boards might not reverse   Unknown                    N                        
          7  AAAAAAAAHAAAAAAA                   2450123        2450132        14527   1.0000000E+003                  1  ation                      N                          N                          N                          N                          N                          N                          N                          N                          Effects used to prefer ho  Unknown                    N                        
          8  AAAAAAAAIAAAAAAA                   2450350        2450409          304   1.0000000E+003                  1  eing                       Y                          N                          N                          N                          N                          N                          N                          N                          Offences feel only on a f  Unknown                    N                        
          9  AAAAAAAAJAAAAAAA                   2450192        2450248         3439   1.0000000E+003                  1  n st                       Y                          N                          N                          N                          N                          N                          N                          N                          External forces shall com  Unknown                    N                        
         10  AAAAAAAAKAAAAAAA                   2450324        2450365         3314   1.0000000E+003                  1  bar                        N                          N                          N                          N                          N                          N                          N                          N                          Only local achievements u  Unknown                    N                        
         11  AAAAAAAALAAAAAAA                         ?              ?            ?   1.0000000E+003                  ?  ought                                                                                                                                  N                                                     N                          N                          Teachers shall not make u  Unknown                    N                        
         12  AAAAAAAAMAAAAAAA                   2450153        2450169         6688   1.0000000E+003                  1  able                       N                          N                          N                          N                          N                          N                          N                          N                          Also only times would com  Unknown                    N                        
         13  AAAAAAAANAAAAAAA                   2450316        2450365        11354   1.0000000E+003                  1  pri                        N                          N                          N                          N                          N                          N                          N                          N                          Years shall not go later   Unknown                    N                        
         14  AAAAAAAAOAAAAAAA                   2450236        2450282        14480   1.0000000E+003                  1  ese                        N                          N                          N                          N                          N                          N                          N                          N                          Adults would not delay ra  Unknown                    N                        
         15  AAAAAAAAPAAAAAAA                         ?              ?            ?   1.0000000E+003                  ?  anti                       Y                                                     N                          N                                                     N                          N                          N                          Old elements would shake   Unknown                    N                        
         16  AAAAAAAAABAAAAAA                   2450342        2450351        11899   1.0000000E+003                  1  cally                      Y                          N                          N                          N                          N                          N                          N                          N                          Sudden, wooden theories w  Unknown                    N                        
         17  AAAAAAAABBAAAAAA                   2450360        2450406        15529   1.0000000E+003                  1  ation                      Y                          N                          N                          N                          N                          N                          N                          N                          Almost old churches ought  Unknown                    N                        
         18  AAAAAAAACBAAAAAA                   2450581        2450592         8599   1.0000000E+003                  1  eing                       Y                          N                          N                          N                          N                          N                          N                          N                          National communities use   Unknown                    N                        
         19  AAAAAAAADBAAAAAA                   2450623        2450640         5185   1.0000000E+003                  1  n st                       N                          N                          N                          N                          N                          N                          N                          N                          Young families act. Most   Unknown                    N                        
         20  AAAAAAAAEBAAAAAA                   2450531        2450545        17860   1.0000000E+003                  1  bar                        Y                          N                          N                          N                          N                          N                          N                          N                          Usually common courses fi  Unknown                    N                        

--- 20 row(s) selected.
>>
>>-- some negative tests
>>insert into hive.ins_promotion (p_promo_sk, p_item_sk) select * from hive.promotion;

*** ERROR[4223] Target column list for insert into Hive table not supported in this software version.

*** ERROR[8822] The statement was not prepared.

>>-- target column list is not supported
>>
>>insert into hive.ins_promotion select *, p_promo_sk from hive.promotion;

*** ERROR[4023] The degree of each row value constructor (20) must equal the degree of the target table column list (19).

*** ERROR[8822] The statement was not prepared.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[4023] The degree of each row value constructor (20) must equal the degree of the target table column list (19).

>>-- number of columns doesn't match
>>
>>prepare s from
+>insert into hive.ins_time_dim values (current_time, 2, 3, 4, 5, 6, 'c', 'd', 'e', 'f');

*** ERROR[4039] Column T_TIME_SK is of type INTEGER, incompatible with the value's type, TIME(0).

*** ERROR[8822] The statement was not prepared.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[4039] Column T_TIME_SK is of type INTEGER, incompatible with the value's type, TIME(0).

>>-- wrong data types
>>
>>
>>--try new HIVE SYNTAX
>>--------------
>>insert into TABLE hive.ins_customer_address select * from hive.customer_address;

--- 50000 row(s) inserted.
>>
>>select count(*) from hive.customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>select count(*) from hive.ins_customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>
>>insert OVERWRITE TABLE hive.ins_customer_address select * from hive.customer_address;

--- 50000 row(s) inserted.
>>
>>select count(*) from hive.customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>select count(*) from hive.ins_customer_address;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493660577, failedModTS = 1493660579, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/ins_customer_address

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>--execute  again
>>insert OVERWRITE TABLE hive.ins_customer_address select * from hive.customer_address;

--- 50000 row(s) inserted.
>>
>>select count(*) from hive.customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>select count(*) from hive.ins_customer_address;

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>
>>-------------------
>>
>>prepare s from insert into TABLE hive.ins_store select * from hive.store;

--- SQL command prepared.
>>execute s;

--- 12 row(s) inserted.
>>select count(*) from hive.store;

(EXPR)              
--------------------

                  12

--- 1 row(s) selected.
>>select count(*) from hive.ins_store;

(EXPR)              
--------------------

                  12

--- 1 row(s) selected.
>>-- Now for the following inserts don't specify a schema
>>prepare s from insert into ins_store_sales select * from store_sales;

--- SQL command prepared.
>>execute s;

--- 2880404 row(s) inserted.
>>select count(*) from hive.store_sales;

(EXPR)              
--------------------

             2880404

--- 1 row(s) selected.
>>select count(*) from hive.ins_store_sales;

(EXPR)              
--------------------

             2880404

--- 1 row(s) selected.
>>prepare s1 from select [first 1] 'the first row' from ins_store_sales;

--- SQL command prepared.
>>execute s1;

(EXPR)       
-------------

the first row

--- 1 row(s) selected.
>>prepare s1 from values('lp 1425661 - Hang with hive scan and [FIRST N]');

--- SQL command prepared.
>>execute s1;

(EXPR)                                        
----------------------------------------------

lp 1425661 - Hang with hive scan and [FIRST N]

--- 1 row(s) selected.
>>
>>prepare s from insert OVERWRITE TABLE ins_customer_demographics select * from customer_demographics;

--- SQL command prepared.
>>execute s;

--- 1920800 row(s) inserted.
>>select count(*) from hive.customer_demographics;

(EXPR)              
--------------------

             1920800

--- 1 row(s) selected.
>>select count(*) from hive.ins_customer_demographics;

(EXPR)              
--------------------

             1920800

--- 1 row(s) selected.
>>
>>prepare s from insert into hive.ins_date_dim select * from hive.date_dim;

--- SQL command prepared.
>>execute s;

--- 73049 row(s) inserted.
>>select count(*) from hive.date_dim;

(EXPR)              
--------------------

               73049

--- 1 row(s) selected.
>>select count(*) from hive.ins_date_dim;

(EXPR)              
--------------------

               73049

--- 1 row(s) selected.
>>
>>prepare s from insert into hive.ins_time_dim select * from hive.time_dim;

--- SQL command prepared.
>>execute s;

--- 86400 row(s) inserted.
>>select count(*) from hive.time_dim;

(EXPR)              
--------------------

               86400

--- 1 row(s) selected.
>>select count(*) from hive.ins_time_dim;

(EXPR)              
--------------------

               86400

--- 1 row(s) selected.
>>
>>prepare s from insert into hive.ins_item select * from item;

--- SQL command prepared.
>>execute s;

--- 18000 row(s) inserted.
>>select count(*) from hive.item;

(EXPR)              
--------------------

               18000

--- 1 row(s) selected.
>>select count(*) from hive.ins_item;

(EXPR)              
--------------------

               18000

--- 1 row(s) selected.
>>
>>prepare s from insert into ins_household_demographics select * from hive.household_demographics;

--- SQL command prepared.
>>execute s;

--- 7200 row(s) inserted.
>>select count(*) from hive.household_demographics;

(EXPR)              
--------------------

                7200

--- 1 row(s) selected.
>>select count(*) from hive.ins_household_demographics;

(EXPR)              
--------------------

                7200

--- 1 row(s) selected.
>>
>>--select [first 100] * from ins_date_dim order by d_date_sk;
>>select [first 100] * from ins_time_dim order by t_time_sk;

T_TIME_SK    T_TIME_ID                  T_TIME       T_HOUR       T_MINUTE     T_SECOND     T_AM_PM                    T_SHIFT                    T_SUB_SHIFT                T_MEAL_TIME
-----------  -------------------------  -----------  -----------  -----------  -----------  -------------------------  -------------------------  -------------------------  -------------------------

          0  AAAAAAAABAAAAAAA                     0            0            0            0  AM                         third                      night                                               
          1  AAAAAAAACAAAAAAA                     1            0            0            1  AM                         third                      night                                               
          2  AAAAAAAADAAAAAAA                     2            0            0            2  AM                         third                      night                                               
          3  AAAAAAAAEAAAAAAA                     3            0            0            3  AM                         third                      night                                               
          4  AAAAAAAAFAAAAAAA                     4            0            0            4  AM                         third                      night                                               
          5  AAAAAAAAGAAAAAAA                     5            0            0            5  AM                         third                      night                                               
          6  AAAAAAAAHAAAAAAA                     6            0            0            6  AM                         third                      night                                               
          7  AAAAAAAAIAAAAAAA                     7            0            0            7  AM                         third                      night                                               
          8  AAAAAAAAJAAAAAAA                     8            0            0            8  AM                         third                      night                                               
          9  AAAAAAAAKAAAAAAA                     9            0            0            9  AM                         third                      night                                               
         10  AAAAAAAALAAAAAAA                    10            0            0           10  AM                         third                      night                                               
         11  AAAAAAAAMAAAAAAA                    11            0            0           11  AM                         third                      night                                               
         12  AAAAAAAANAAAAAAA                    12            0            0           12  AM                         third                      night                                               
         13  AAAAAAAAOAAAAAAA                    13            0            0           13  AM                         third                      night                                               
         14  AAAAAAAAPAAAAAAA                    14            0            0           14  AM                         third                      night                                               
         15  AAAAAAAAABAAAAAA                    15            0            0           15  AM                         third                      night                                               
         16  AAAAAAAABBAAAAAA                    16            0            0           16  AM                         third                      night                                               
         17  AAAAAAAACBAAAAAA                    17            0            0           17  AM                         third                      night                                               
         18  AAAAAAAADBAAAAAA                    18            0            0           18  AM                         third                      night                                               
         19  AAAAAAAAEBAAAAAA                    19            0            0           19  AM                         third                      night                                               
         20  AAAAAAAAFBAAAAAA                    20            0            0           20  AM                         third                      night                                               
         21  AAAAAAAAGBAAAAAA                    21            0            0           21  AM                         third                      night                                               
         22  AAAAAAAAHBAAAAAA                    22            0            0           22  AM                         third                      night                                               
         23  AAAAAAAAIBAAAAAA                    23            0            0           23  AM                         third                      night                                               
         24  AAAAAAAAJBAAAAAA                    24            0            0           24  AM                         third                      night                                               
         25  AAAAAAAAKBAAAAAA                    25            0            0           25  AM                         third                      night                                               
         26  AAAAAAAALBAAAAAA                    26            0            0           26  AM                         third                      night                                               
         27  AAAAAAAAMBAAAAAA                    27            0            0           27  AM                         third                      night                                               
         28  AAAAAAAANBAAAAAA                    28            0            0           28  AM                         third                      night                                               
         29  AAAAAAAAOBAAAAAA                    29            0            0           29  AM                         third                      night                                               
         30  AAAAAAAAPBAAAAAA                    30            0            0           30  AM                         third                      night                                               
         31  AAAAAAAAACAAAAAA                    31            0            0           31  AM                         third                      night                                               
         32  AAAAAAAABCAAAAAA                    32            0            0           32  AM                         third                      night                                               
         33  AAAAAAAACCAAAAAA                    33            0            0           33  AM                         third                      night                                               
         34  AAAAAAAADCAAAAAA                    34            0            0           34  AM                         third                      night                                               
         35  AAAAAAAAECAAAAAA                    35            0            0           35  AM                         third                      night                                               
         36  AAAAAAAAFCAAAAAA                    36            0            0           36  AM                         third                      night                                               
         37  AAAAAAAAGCAAAAAA                    37            0            0           37  AM                         third                      night                                               
         38  AAAAAAAAHCAAAAAA                    38            0            0           38  AM                         third                      night                                               
         39  AAAAAAAAICAAAAAA                    39            0            0           39  AM                         third                      night                                               
         40  AAAAAAAAJCAAAAAA                    40            0            0           40  AM                         third                      night                                               
         41  AAAAAAAAKCAAAAAA                    41            0            0           41  AM                         third                      night                                               
         42  AAAAAAAALCAAAAAA                    42            0            0           42  AM                         third                      night                                               
         43  AAAAAAAAMCAAAAAA                    43            0            0           43  AM                         third                      night                                               
         44  AAAAAAAANCAAAAAA                    44            0            0           44  AM                         third                      night                                               
         45  AAAAAAAAOCAAAAAA                    45            0            0           45  AM                         third                      night                                               
         46  AAAAAAAAPCAAAAAA                    46            0            0           46  AM                         third                      night                                               
         47  AAAAAAAAADAAAAAA                    47            0            0           47  AM                         third                      night                                               
         48  AAAAAAAABDAAAAAA                    48            0            0           48  AM                         third                      night                                               
         49  AAAAAAAACDAAAAAA                    49            0            0           49  AM                         third                      night                                               
         50  AAAAAAAADDAAAAAA                    50            0            0           50  AM                         third                      night                                               
         51  AAAAAAAAEDAAAAAA                    51            0            0           51  AM                         third                      night                                               
         52  AAAAAAAAFDAAAAAA                    52            0            0           52  AM                         third                      night                                               
         53  AAAAAAAAGDAAAAAA                    53            0            0           53  AM                         third                      night                                               
         54  AAAAAAAAHDAAAAAA                    54            0            0           54  AM                         third                      night                                               
         55  AAAAAAAAIDAAAAAA                    55            0            0           55  AM                         third                      night                                               
         56  AAAAAAAAJDAAAAAA                    56            0            0           56  AM                         third                      night                                               
         57  AAAAAAAAKDAAAAAA                    57            0            0           57  AM                         third                      night                                               
         58  AAAAAAAALDAAAAAA                    58            0            0           58  AM                         third                      night                                               
         59  AAAAAAAAMDAAAAAA                    59            0            0           59  AM                         third                      night                                               
         60  AAAAAAAANDAAAAAA                    60            0            1            0  AM                         third                      night                                               
         61  AAAAAAAAODAAAAAA                    61            0            1            1  AM                         third                      night                                               
         62  AAAAAAAAPDAAAAAA                    62            0            1            2  AM                         third                      night                                               
         63  AAAAAAAAAEAAAAAA                    63            0            1            3  AM                         third                      night                                               
         64  AAAAAAAABEAAAAAA                    64            0            1            4  AM                         third                      night                                               
         65  AAAAAAAACEAAAAAA                    65            0            1            5  AM                         third                      night                                               
         66  AAAAAAAADEAAAAAA                    66            0            1            6  AM                         third                      night                                               
         67  AAAAAAAAEEAAAAAA                    67            0            1            7  AM                         third                      night                                               
         68  AAAAAAAAFEAAAAAA                    68            0            1            8  AM                         third                      night                                               
         69  AAAAAAAAGEAAAAAA                    69            0            1            9  AM                         third                      night                                               
         70  AAAAAAAAHEAAAAAA                    70            0            1           10  AM                         third                      night                                               
         71  AAAAAAAAIEAAAAAA                    71            0            1           11  AM                         third                      night                                               
         72  AAAAAAAAJEAAAAAA                    72            0            1           12  AM                         third                      night                                               
         73  AAAAAAAAKEAAAAAA                    73            0            1           13  AM                         third                      night                                               
         74  AAAAAAAALEAAAAAA                    74            0            1           14  AM                         third                      night                                               
         75  AAAAAAAAMEAAAAAA                    75            0            1           15  AM                         third                      night                                               
         76  AAAAAAAANEAAAAAA                    76            0            1           16  AM                         third                      night                                               
         77  AAAAAAAAOEAAAAAA                    77            0            1           17  AM                         third                      night                                               
         78  AAAAAAAAPEAAAAAA                    78            0            1           18  AM                         third                      night                                               
         79  AAAAAAAAAFAAAAAA                    79            0            1           19  AM                         third                      night                                               
         80  AAAAAAAABFAAAAAA                    80            0            1           20  AM                         third                      night                                               
         81  AAAAAAAACFAAAAAA                    81            0            1           21  AM                         third                      night                                               
         82  AAAAAAAADFAAAAAA                    82            0            1           22  AM                         third                      night                                               
         83  AAAAAAAAEFAAAAAA                    83            0            1           23  AM                         third                      night                                               
         84  AAAAAAAAFFAAAAAA                    84            0            1           24  AM                         third                      night                                               
         85  AAAAAAAAGFAAAAAA                    85            0            1           25  AM                         third                      night                                               
         86  AAAAAAAAHFAAAAAA                    86            0            1           26  AM                         third                      night                                               
         87  AAAAAAAAIFAAAAAA                    87            0            1           27  AM                         third                      night                                               
         88  AAAAAAAAJFAAAAAA                    88            0            1           28  AM                         third                      night                                               
         89  AAAAAAAAKFAAAAAA                    89            0            1           29  AM                         third                      night                                               
         90  AAAAAAAALFAAAAAA                    90            0            1           30  AM                         third                      night                                               
         91  AAAAAAAAMFAAAAAA                    91            0            1           31  AM                         third                      night                                               
         92  AAAAAAAANFAAAAAA                    92            0            1           32  AM                         third                      night                                               
         93  AAAAAAAAOFAAAAAA                    93            0            1           33  AM                         third                      night                                               
         94  AAAAAAAAPFAAAAAA                    94            0            1           34  AM                         third                      night                                               
         95  AAAAAAAAAGAAAAAA                    95            0            1           35  AM                         third                      night                                               
         96  AAAAAAAABGAAAAAA                    96            0            1           36  AM                         third                      night                                               
         97  AAAAAAAACGAAAAAA                    97            0            1           37  AM                         third                      night                                               
         98  AAAAAAAADGAAAAAA                    98            0            1           38  AM                         third                      night                                               
         99  AAAAAAAAEGAAAAAA                    99            0            1           39  AM                         third                      night                                               

--- 100 row(s) selected.
>>--------------------------------------------------
>>--insert using parallel queries and overwrite 
>>-----------------------
>>cqd attempt_esp_parallelism 'on';

--- SQL operation complete.
>>cqd PARALLEL_NUM_ESPS '2';

--- SQL operation complete.
>>set schema hive;

--- SQL operation complete.
>>cqd hive_max_esps '2';

--- SQL operation complete.
>>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '25' ;

--- SQL operation complete.
>>truncate ins_store_sales_summary;

--- SQL operation complete.
>>control query shape esp_exchange(cut);

--- SQL operation complete.
>>prepare s from insert into table ins_store_sales_summary select ss_sold_date_sk,ss_store_sk, sum (ss_quantity) from store_sales group by  ss_sold_date_sk ,ss_store_sk;

*** WARNING[6008] Statistics for column (SS_SOLD_DATE_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (SS_STORE_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (SS_SOLD_DATE_SK, SS_STORE_SK) from table HIVE.HIVE.STORE_SALES were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

6    .    7    root                                                  2.92E+006
5    .    6    esp_exchange                    1:2(hash2)            2.92E+006
3    .    5    hive_insert                     INS_STORE_SALES_SUMM  2.92E+006
2    .    3    hash_groupby                                          2.92E+006
1    .    2    esp_exchange                    2(hash2):2(hash2)     2.92E+006
.    .    1    hive_scan                       STORE_SALES           2.92E+006

--- SQL operation complete.
>>execute s;

--- 12768 row(s) inserted.
>>control query shape cut;

--- SQL operation complete.
>>select [first 12] 'test lp bug # 1355477' from ins_store_sales_summary;

(EXPR)               
---------------------

test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477
test lp bug # 1355477

--- 12 row(s) selected.
>>
>>-- hadoop ls should return 2 files
>>-- sh regrhadoop.ksh fs -ls  /user/trafodion/hive/exttables/ins_store_sales_summary/* | grep ins_store_sales_summary | wc -l | tee -a LOG003;
>>log;
2
1c1
< >>select ss_sold_date_sk,ss_store_sk, sum (ss_quantity)  from store_sales group by  ss_sold_date_sk ,ss_store_sk order by  ss_sold_date_sk,ss_store_sk;
---
> >>select  * from ins_store_sales_summary order by  ss_sold_date_sk,ss_store_sk;
3,10c3,4
< *** WARNING[6008] Statistics for column (SS_SOLD_DATE_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.
< 
< *** WARNING[6008] Statistics for column (SS_STORE_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.
< 
< *** WARNING[6007] Multi-column statistics for columns (SS_SOLD_DATE_SK, SS_STORE_SK) from table HIVE.HIVE.STORE_SALES were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.
< 
< SS_SOLD_DATE_SK  SS_STORE_SK  (EXPR)              
< ---------------  -----------  --------------------
---
> SS_SOLD_DATE_SK  SS_STORE_SK  SS_QUANTITY
> ---------------  -----------  -----------
>>--execute again --overwrite should get rid og existing data from previous run
>>control query shape union(cut, esp_exchange(cut));

--- SQL operation complete.
>>prepare s from insert overwrite table ins_store_sales_summary select ss_sold_date_sk,ss_store_sk, sum (ss_quantity) from store_sales group by  ss_sold_date_sk ,ss_store_sk;

*** WARNING[6008] Statistics for column (SS_SOLD_DATE_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (SS_STORE_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6007] Multi-column statistics for columns (SS_SOLD_DATE_SK, SS_STORE_SK) from table HIVE.HIVE.STORE_SALES were not available. The columns were being used by GroupBy operator. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

8    .    9    root                                                  2.92E+006
1    7    8    blocked_union                                         2.92E+006
6    .    7    esp_exchange                    1:2(hash2)            2.92E+006
4    .    6    hive_insert                     INS_STORE_SALES_SUMM  2.92E+006
3    .    4    hash_groupby                                          2.92E+006
2    .    3    esp_exchange                    2(hash2):2(hash2)     2.92E+006
.    .    2    hive_scan                       STORE_SALES           2.92E+006
.    .    1    hive_truncate                                         1.00E+000

--- SQL operation complete.
>>execute s;

--- 12768 row(s) inserted.
>>control query shape cut;

--- SQL operation complete.
>>log;
1c1
< >>select ss_sold_date_sk,ss_store_sk, sum (ss_quantity)  from store_sales group by  ss_sold_date_sk ,ss_store_sk order by  ss_sold_date_sk,ss_store_sk;
---
> >>select  * from ins_store_sales_summary order by  ss_sold_date_sk,ss_store_sk;
3,4c3,8
< SS_SOLD_DATE_SK  SS_STORE_SK  (EXPR)              
< ---------------  -----------  --------------------
---
> *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
> 
> *** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493660691, failedModTS = 1493660717, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/ins_store_sales_summary
> 
> SS_SOLD_DATE_SK  SS_STORE_SK  SS_QUANTITY
> ---------------  -----------  -----------
