| >> |
| >>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 or edition. |
| |
| *** 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[4035] Type TIME(0) cannot be cast to type INTEGER. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- 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 |
| > --------------- ----------- ----------- |
