| -- Test: TEST130 (Executor) |
| -- @@@ START COPYRIGHT @@@ |
| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, |
| -- software distributed under the License is distributed on an |
| -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| -- KIND, either express or implied. See the License for the |
| -- specific language governing permissions and limitations |
| -- under the License. |
| -- |
| -- @@@ END COPYRIGHT @@@ |
| -- |
| -- Functionality: LOBs |
| -- Expected files: EXPECTED130 |
| -- Table created: |
| -- Limitations: |
| -- |
| -- To Do: |
| -- |
| -- Revision history: |
| -- (10/21/2014) - Created. |
| -- |
| |
| -- remove after this is made the default. |
| cqd TRAF_BLOB_AS_VARCHAR 'OFF'; |
| cqd TRAF_CLOB_AS_VARCHAR 'OFF'; |
| |
| |
| ?section clnup |
| set schema trafodion.lob130; |
| drop table t130lob1 cascade; |
| drop table t130lob2 cascade; |
| drop table t130lob3 cascade; |
| drop table t130lob4 cascade; |
| drop table t130lob5 cascade; |
| drop table tlob130ext cascade; |
| drop table t130char cascade; |
| drop table t130var cascade; |
| drop external table if exists date_dim for hive.hive.date_dim; |
| |
| ?section setup |
| log LOG130 clear; |
| log; |
| drop schema trafodion.lob130 cascade; |
| log LOG130; |
| create schema trafodion.lob130; |
| set schema trafodion.lob130; |
| create table t130lob1 (c1 blob); |
| create table t130lob2 (c1 int not null, c2 blob , primary key (c1)); |
| create table t130lob3 (c1 int not null, |
| c2 blob, c3 blob, primary key (c1)); |
| create table t130lob4 (c1 int not null, |
| c2 char(10), c3 clob, primary key (c1)); |
| create table t130char(c1 int not null, c2 char (20), primary key (c1)); |
| create table t130var(c1 int not null, c2 varchar(100), primary key (c1)); |
| create external table date_dim |
| (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date) |
| for hive.hive.date_dim; |
| |
| ?section dml_insert |
| insert into t130lob1 values(NULL); |
| select * from t130lob1; |
| |
| insert into t130lob2 values(1,stringtolob('inserted row11')); |
| insert into t130lob2 values(2,stringtolob('inserted row12')); |
| insert into t130lob2 values(3,stringtolob('inserted row13')); |
| insert into t130lob2 values(3,stringtolob('xxxx')); |
| select * from t130lob2; |
| |
| |
| select lobtostring(c2,10) from t130lob2; |
| select lobtostring(c2,2) from t130lob2; |
| select c1,lobtostring(c2,100) from t130lob2; |
| |
| delete from t130lob2; |
| --test insert with param (assumes that caller will pass in string format.) |
| prepare s from insert into t130lob2 values (1, ?); |
| execute s using 'fgfgfhfhfhfhhfhfhfhjfkkfkffllflflfll'; |
| select lobtostring(c2,50) from t130lob2; |
| |
| --negative test for values tuple list |
| prepare s from insert into t130lob2 values (1,'gg'),(2,'hh'); |
| prepare s from insert into t130lob2 values (1,stringtolob('hh')),(2,stringtolob('gg')); |
| |
| --test update with param |
| prepare s from update t130lob2 set c2=? where c1 =1; |
| execute s using 'xyxyxyxyxyxyx'; |
| select lobtostring(c2,50) from t130lob2; |
| |
| --test insert select from a source char column to lob |
| delete from t130lob2; |
| insert into t130char values (1,'fixed char 1'),(2,'fixed char 2'),(3,'fixed char 3'); |
| insert into t130lob2(c1,c2) select c1,c2 from t130char; |
| select lobtostring(c2,50) from t130lob2; |
| |
| --test insert select from a source varchar column to lob |
| delete from t130lob2; |
| insert into t130var values (1,'var char 1'),(2,'var char 22222222222222222222222'),(3,'var char 33333333333333333333333333333333333333333333333333333'); |
| insert into t130lob2(c1,c2) select c1,c2 from t130var; |
| select lobtostring(c2,100) from t130lob2; |
| |
| --insert select from a source lob column to a target varchar column. |
| delete from t130var; |
| insert into t130var select c1, lobtostring(c2,100) from t130lob2; |
| delete from t130var; |
| ----negative case |
| insert into t130var select c1,c2 from t130lob2; |
| delete from t130var; |
| delete from t130lob2; |
| |
| --test insert select from a source hive varchar column to lob |
| delete from t130lob2; |
| insert into t130lob2 select [first 10] d_date_sk,d_date_id from hive.hive.date_dim; |
| select lobtostring(c2,4) from t130lob2 order by c1; |
| delete from t130lob2; |
| |
| |
| --insert select from a source lob column to a target lob column |
| delete from t130lob3; |
| insert into t130lob2 values(1,stringtolob('inserted row11')); |
| insert into t130lob2 values(2,stringtolob('inserted row12')); |
| insert into t130lob2 values(3,stringtolob('inserted row13')); |
| insert into t130lob3 select c1,c2,c2 from t130lob2; |
| select c1, lobtostring(c2,100),lobtostring(c3,100) from t130lob3; |
| delete from t130lob3; |
| delete from t130lob2; |
| |
| |
| |
| |
| --test values clause |
| values((select * from t130lob1)); |
| |
| --test empty_blob(), empty_clob() |
| delete from t130lob2; |
| insert into t130lob2 values (1, empty_blob()); |
| select lobtostring(c2,10) from t130lob2; |
| delete from t130lob2; |
| insert into t130lob2 values (1, empty_clob()); |
| select lobtostring(c2,10) from t130lob2; |
| update t130lob2 set c2=stringtolob('inserted row11'); |
| select lobtostring(c2,30) from t130lob2; |
| update t130lob2 set c2=empty_blob(); |
| select lobtostring(c2,10) from t130lob2; |
| update t130lob2 set c2=stringtolob('inserted row11',append); |
| select lobtostring(c2,30) from t130lob2; |
| update t130lob2 set c2=stringtolob('more inserted row11',append); |
| select lobtostring(c2,50) from t130lob2; |
| update t130lob2 set c2=empty_blob(); |
| select lobtostring(c2,10) from t130lob2; |
| |
| ?section dml_join |
| insert into t130lob3 values (1,stringtolob('inserted row21a'),stringtolob('inserted row21b')); |
| insert into t130lob3 values (2,stringtolob('inserted row22a'),stringtolob('inserted row22b')); |
| insert into t130lob3 values (3,stringtolob('inserted row23a'),stringtolob('inserted row23b')); |
| insert into t130lob3 values (4,stringtolob('inserted row24a'),stringtolob('inserted row24b')); |
| |
| prepare s from |
| select c1, lobtostring(c2,25), lobtostring(c3,25) from t130lob3; |
| |
| |
| |
| select lobtostring(t130lob2.c2,30) from t130lob2, t130lob3 where t130lob2.c1 = t130lob3.c1; |
| |
| values (filetolob('./myfile.txt')); |
| values( stringtolob('xxxxxx')); |
| |
| ?section dml_update |
| |
| update t130lob2 set c2=stringtolob('updated c2 in all rows'); |
| select c1, lobtostring(c2,30) from t130lob2; |
| |
| update t130lob2 set c2=stringtolob('updated row21a') where c1=1; |
| select c1, lobtostring(c2,30) from t130lob2; |
| select c1, lobtostring(c2,30) from t130lob2 where c1=1; |
| |
| |
| ?section dml_update_append |
| |
| update t130lob2 set c2=stringtolob('appended row21a',append) where c1=1; |
| select c1, lobtostring(c2,100) from t130lob2; |
| select c1, lobtostring(c2,100) from t130lob2 where c1=1; |
| |
| update t130lob2 set c2=stringtolob(' appended c2 to all rows',append); |
| select c1, lobtostring(c2,60) from t130lob2; |
| |
| ?section dml_delete |
| |
| delete from t130lob3 where c1=1; |
| select c1, lobtostring(c2,30), lobtostring(c3,30) from t130lob3; |
| |
| delete from t130lob2 ; |
| select * from t130lob2; |
| |
| ?section update_stats |
| |
| -- positive tests; LOB columns should be silently skipped |
| update statistics for table t130lob1 on every column; |
| |
| update statistics for table t130lob4 on c1 to c2; |
| |
| -- negative tests; attempts to explicitly create stats on LOB |
| -- columns should fail with error 9246 |
| update statistics for table t130lob4 on c3; |
| |
| update statistics for table t130lob2 on (c1,c2); |
| |
| update statistics for table t130lob4 on c2 to c3; |
| |
| log; |
| obey TEST130(clnup); |
| |
| |
| sh echo "Test for file input and extract"; |
| -- Test for local input |
| |
| --cleanup -- comment the following lines out if you want to debug this test and save intermediate files. |
| |
| ?section lob_local_cleanup |
| log; |
| drop table tlob130txt1; |
| drop table tlob130bin1; |
| drop table tlob130_limit50; |
| sh rm TMP130; |
| sh rm tlob130txt1; |
| sh rm tlob130_txt1.txt; |
| sh rm tlob130_deep.jpg; |
| sh rm tlob130_anoush.jpg; |
| |
| |
| ?section lob_local_file |
| log LOG130; |
| --setup |
| sh echo "Test for file input and extract"; |
| create table tlob130txt1 (c1 int not null, c2 clob, primary key (c1)); |
| create table tlob130bin1 (c1 int not null, c2 blob, primary key (c1)); |
| create table tlob130txt_limit50(c1 int not null, c2 clob(50), primary key (c1)); |
| create table tlob130bin_limit1K(c1 int not null, c2 blob(1 K), primary key (c1)); |
| |
| sh cp $scriptsdir/executor/deep.jpg $rundir/executor/; |
| sh cp $scriptsdir/executor/anoush.jpg $rundir/executor/; |
| sh cp $scriptsdir/executor/lob_input_* $rundir/executor/; |
| sh cp $scriptsdir/executor/TEST130_argfile* $rundir/executor/; |
| |
| -- inserts |
| -- first line |
| insert into tlob130txt1 values (1, filetolob('lob_input_a1.txt')); |
| |
| -- second line |
| insert into tlob130txt1 values (2, filetolob('lob_input_b1.txt')); |
| |
| -- third line |
| insert into tlob130txt1 values (3, filetolob('lob_input_c1.txt')); |
| |
| select lobtostring(c2, 40 ) from tlob130txt1; |
| |
| --updates |
| |
| --should update with full poem |
| update tlob130txt1 set c2=filetolob('lob_input_d1.txt', append) where c1 = 3; |
| |
| select lobtostring(c2, 200 ) from tlob130txt1; |
| |
| -- should see wrong text in the last few lines |
| update tlob130txt1 set c2=filetolob('lob_input_e1.txt') where c1 =3 ; |
| |
| select lobtostring(c2, 200 ) from tlob130txt1; |
| |
| --delete |
| |
| -- go back to having just the first line. |
| delete from tlob130txt1 where c1 =2; |
| delete from tlob130txt1 where c1 =3; |
| |
| --test limits |
| |
| insert into tlob130txt_limit50 values(1,filetolob('lob_input_e1.txt')); |
| insert into tlob130bin_limit1K values(1,filetolob('anoush.jpg')); |
| --test extract |
| |
| log; |
| log TMP130 clear; |
| select c2 from tlob130txt1; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_txt1.txt');/g" >> t130_extract_command; |
| |
| obey t130_extract_command; |
| |
| --binary input/update |
| |
| insert into tlob130bin1 values (1 , filetolob('deep.jpg')); |
| --extract // should have a viewable picture file |
| |
| log; |
| log TMP130 clear; |
| select c2 from tlob130bin1; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_deep.jpg');/g" >> t130_extract_command; |
| obey t130_extract_command; |
| |
| update tlob130bin1 set c2=filetolob('anoush.jpg') ; |
| |
| --extract into a different file // should have a different viewable picture. |
| |
| log; |
| log TMP130 clear ; |
| select c2 from tlob130bin1; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_anoush.jpg');/g" >> t130_extract_command; |
| |
| obey t130_extract_command; |
| |
| |
| |
| |
| -- Test for hdfs input |
| --cleanup -- comment the following lines out if you want to debug this test and save intermediate files. |
| |
| ?section lob_hdfs_cleanup |
| log; |
| drop table tlob130txt2; |
| drop table tlob130bin2; |
| sh rm TMP130; |
| sh rm tlob130txt2; |
| sh rm tlob130_txt2.txt; |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_deep.jpg; |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_anoush.jpg; |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_txt2.txt; |
| |
| |
| ?section lob_hdfs_file |
| log LOG130; |
| create table tlob130txt2 (c1 int not null, c2 clob, primary key (c1)); |
| create table tlob130bin2 (c1 int not null, c2 blob, primary key (c1)); |
| |
| sh regrhadoop.ksh fs -copyFromLocal lob_input_a1.txt /user/trafodion/lobs/lob_input_a1.txt; |
| sh regrhadoop.ksh fs -copyFromLocal lob_input_b1.txt /user/trafodion/lobs/lob_input_b1.txt; |
| sh regrhadoop.ksh fs -copyFromLocal lob_input_c1.txt /user/trafodion/lobs/lob_input_c1.txt; |
| sh regrhadoop.ksh fs -copyFromLocal lob_input_d1.txt /user/trafodion/lobs/lob_input_d1.txt; |
| sh regrhadoop.ksh fs -copyFromLocal lob_input_e1.txt /user/trafodion/lobs/lob_input_e1.txt; |
| sh regrhadoop.ksh fs -copyFromLocal deep.jpg /user/trafodion/lobs/deep.jpg; |
| sh regrhadoop.ksh fs -copyFromLocal anoush.jpg /user/trafodion/lobs/anoush.jpg; |
| -- the next one is a really long file name intended to test error message 8557 |
| sh regrhadoop.ksh fs -copyFromLocal lob_input_a1.txt /user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.txt; |
| sh sleep(20); |
| |
| |
| insert into tlob130txt2 values (1, filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); |
| |
| -- second line |
| insert into tlob130txt2 values (2, filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt')); |
| |
| -- third line |
| insert into tlob130txt2 values (3, filetolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt')); |
| select lobtostring(c2, 40 ) from tlob130txt2; |
| |
| --updates |
| |
| --should update with full poem |
| update tlob130txt2 set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_d1.txt', append) where c1 = 3; |
| select lobtostring(c2, 200 ) from tlob130txt2; |
| |
| -- should see wrong text in the last few lines |
| update tlob130txt2 set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_e1.txt') where c1 =3 ; |
| select lobtostring(c2, 200 ) from tlob130txt2; |
| |
| --delete |
| |
| |
| -- go back to having just the first line. |
| delete from tlob130txt2 where c1 =2; |
| delete from tlob130txt2 where c1 =3; |
| -- test extract |
| |
| log; |
| log TMP130 clear; |
| select c2 from tlob130txt2; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user/trafodion/lobs\/tlob130_txt2.txt');/g" >> t130_extract_command; |
| |
| obey t130_extract_command; |
| |
| --binary input/update |
| |
| insert into tlob130bin2 values (1 , filetolob('deep.jpg')); |
| --extract // should have a viewable picture file |
| |
| log; |
| log TMP130 clear ; |
| select c2 from tlob130bin2; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user/trafodion/lobs\/tlob130_deep.jpg');/g" >> t130_extract_command; |
| obey t130_extract_command; |
| |
| update tlob130bin2 set c2=filetolob('anoush.jpg') ; |
| |
| --extract into a different file // should have a different viewable picture. |
| |
| log; |
| log TMP130 clear; |
| select c2 from tlob130bin1; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user/trafodion/lobs\/tlob130_anoush.jpg');/g" >> t130_extract_command; |
| |
| obey t130_extract_command; |
| |
| |
| sh clitestdriver 2 < TEST130_argfile 2>&1 | tee -a LOG130; |
| |
| |
| |
| ------------------------------------------------------------------------------ |
| |
| ?section lob_misc_cleanup |
| log; |
| drop table tlob130txt3; |
| drop table tlob130bin3; |
| drop table tlob130bt; |
| sh rm tlob130_deep2.jpg; |
| sh rm tlob130_txt1.txt; |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_anoush2.jpg; |
| sh rm tlob130_anoush3.jpg; |
| |
| |
| ?section lob_miscfile_test |
| log LOG130; |
| -- 2 clob columns |
| create table tlob130txt3 (c1 int not null, c2 int, c3 clob, c4 clob, primary key (c1)); |
| insert into tlob130txt3 values (1, 1,filetolob('lob_input_a1.txt'), filetolob('lob_input_b1.txt')); |
| insert into tlob130txt3 values (2, 2,filetolob('lob_input_c1.txt'), filetolob('lob_input_d1.txt')); |
| select lobtostring(c3,100), lobtostring(c4,100)from tlob130txt3; |
| |
| -- 2 blob columns |
| create table tlob130bin3 (c1 int not null, c2 int, c3 blob, c4 blob, primary key (c1)); |
| insert into tlob130bin3 values (1, 1, filetolob('deep.jpg'), filetolob('anoush.jpg')); |
| |
| log; |
| log TMP130 clear; |
| select c3 from tlob130bin3; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_deep2.jpg');/g" >> t130_extract_command; |
| obey t130_extract_command; |
| |
| log; |
| log TMP130 clear; |
| select c4 from tlob130bin3; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user/trafodion/lobs\/tlob130_anoush2.jpg');/g" >> t130_extract_command; |
| obey t130_extract_command; |
| |
| -- combination blob and clob columns |
| create table tlob130bt (c1 int not null, c2 int, c3 blob, c4 clob, primary key (c1)); |
| insert into tlob130bt values (1,1, filetolob('lob_input_a1.txt'), filetolob('anoush.jpg')); |
| |
| select lobtostring(c3,40) from tlob130bt; |
| |
| log; |
| log TMP130 clear; |
| select c4 from tlob130bt; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_anoush3.jpg',create,truncate);/g" >> t130_extract_command; |
| obey t130_extract_command; |
| |
| ?section lob_gc_cleanup |
| log; |
| drop table tlob130gc; |
| |
| ?section lob_gc_test |
| log LOG130; |
| create table tlob130gc (c1 int not null, c2 blob, c3 blob, primary key (c1)); |
| --insert a few rows |
| insert into tlob130gc values (1, stringtolob('aaaa'), stringtolob('bbbbb')); |
| insert into tlob130gc values (2, stringtolob('aaaa'), stringtolob('bbbbb')); |
| insert into tlob130gc values (3, stringtolob('aaaa'), stringtolob('bbbbb')); |
| insert into tlob130gc values (4, stringtolob('aaaa'), stringtolob('bbbbb')); |
| insert into tlob130gc values (5, stringtolob('aaaa'), stringtolob('bbbbb')); |
| insert into tlob130gc values (6, stringtolob('aaaa'), stringtolob('bbbbb')); |
| insert into tlob130gc values (7, stringtolob('aaaa'), stringtolob('bbbbb')); |
| insert into tlob130gc values (8, stringtolob('aaaa'), stringtolob('bbbbb')); |
| --create holes in the lob data file |
| delete from tlob130gc where c1=2; |
| delete from tlob130gc where c1=6; |
| update tlob130gc set c2=stringtolob('xxxx')where c1=7; |
| --check contents of table |
| select c1,lobtostring(c2,10),lobtostring(c3,10) from tlob130gc; |
| cqd LOB_GC_LIMIT_SIZE '0'; |
| --this next insert should trigger a GC |
| insert into tlob130gc values(9,stringtolob('aaaa'), stringtolob('bbbbb')); |
| --check contents of table. Should have one extra row compared to above select |
| select c1,lobtostring(c2,10),lobtostring(c3,10) from tlob130gc; |
| cqd LOB_GC_LIMIT_SIZE reset; |
| |
| ?section lob_get_cleanup |
| log; |
| drop table tlob130gt; |
| |
| ?section lob_get_test |
| log LOG130; |
| create table tlob130gt (c1 int not null, c2 blob, c3 clob, c4 blob, primary key (c1)); |
| create table tlob130gt2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1)); |
| get lob stats for table tlob130gt2; |
| select * from table(lob stats(tlob130gt2)); |
| insert into tlob130gt values (1, stringtolob('xxxx'), stringtolob('yyyy'), stringtolob('zzzzzzzzzzzzzz')); |
| insert into tlob130gt values (2, stringtolob('xxxxx'), stringtolob('yyyyy'), stringtolob('zzzzzzzzzzzzzzz')); |
| insert into tlob130gt values (3, stringtolob('xxxxxx'), stringtolob('yyyyyy'), stringtolob('zzzzzzzzzzzzzzzz')); |
| get lob stats for table tlob130gt; |
| select * from table(lob stats(tlob130gt)); |
| delete from tlob130gt where c1=2; |
| insert into tlob130gt values (2, stringtolob('xxxxxxxxxxxxxxx'), stringtolob('yyyyyyyyyyyyyyyy'), stringtolob('zzzzzzzzzzzzzzzzzzzzzzzzzz')); |
| get lob stats for table tlob130gt; |
| select * from table(lob stats(tlob130gt)); |
| |
| ?section lob_external |
| --test external lobs |
| create table tlob130ext (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1)); |
| |
| create table tlob130ext2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1)); |
| create table tlob130_not_external (c1 int not null , c2 blob, c3 blob, c4 blob, primary key (c1)); |
| insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); |
| insert into tlob130ext values(2, stringtolob('second lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt')); |
| insert into tlob130ext values(3, stringtolob('third lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt')); |
| |
| |
| log; |
| log TMP130 clear; |
| select c3 from tlob130ext where c1=3; |
| log; |
| |
| log LOG130; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract name(LOB '/g" | sed "s/$/');/g" >> t130_extract_command; |
| obey t130_extract_command; |
| sh rm t130_extract_command; |
| sh grep "^LOBH" TMP130 | sed "s/^/extract offset(LOB '/g" | sed "s/$/');/g" >> t130_extract_command; |
| obey t130_extract_command; |
| |
| |
| |
| |
| --negative cases |
| insert into tlob130ext values(2, externaltolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); |
| insert into tlob130ext values(3, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); |
| update tlob130ext set c4=stringtolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt', append) where c1=1; |
| update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt', append) where c1=1; |
| update tlob130ext set c3=externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt') where c1=1; |
| update tlob130ext set c4=stringtolob('cannot allow this') where c1=1; |
| delete from tlob130ext where c1=1; |
| insert into tlob130ext values(1, stringtolob('first lob'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); |
| insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); |
| -- the next one should see error 8557 |
| insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'), |
| externaltolob('hdfs:///user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.txt')); |
| |
| select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; |
| update tlob130ext set c3=stringtolob('can allow this') where c1=1; |
| select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; |
| |
| |
| |
| update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_d1.txt') where c1=2; |
| select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; |
| |
| |
| update tlob130ext set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt') where c1=2; |
| select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext; |
| get lob stats for table tlob130ext; |
| select * from table(lob stats(tlob130ext)); |
| sh clitestdriver 2 < TEST130_argfile2 2>&1 | tee -a LOG130; |
| sh cat lobc4ext.txt |tee -a LOG130; |
| |
| --insert select from a source external lob column to a target external lob column |
| select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext; |
| insert into tlob130ext2 select * from tlob130ext; |
| select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext2; |
| ----negative test |
| insert into tlob130_not_external select * from tlob130ext; |
| |
| ?section update_lob_handle |
| -- test lob update via "update lob" command |
| log; |
| drop table t130lob5; |
| create table t130lob5 (c1 int not null, c2 blob , primary key (c1)); |
| log LOG130; |
| delete from t130lob5; |
| insert into t130lob5 values (1,empty_blob()); |
| sh clitestdriver 6 < TEST130_argfile3 2>&1 | tee -a LOG130; |
| select lobtostring(c2,20) from t130lob5; |
| sh clitestdriver 7 < TEST130_argfile3 2>&1 | tee -a LOG130; |
| select lobtostring(c2,40) from t130lob5; |
| sh clitestdriver 8 < TEST130_argfile3 2>&1 | tee -a LOG130; |
| select lobtostring(c2,20) from t130lob5; |
| sh clitestdriver 6 < TEST130_argfile3 2>&1 | tee -a LOG130; |
| select lobtostring(c2,40) from t130lob5; |
| delete from t130lob5; |
| |
| |
| -- test_empty_blob() with external tables |
| delete from tlob130ext; |
| -- following empty_blob will be of type 'external' |
| insert into tlob130ext values(1,empty_blob(),empty_clob(),empty_blob()); |
| select c2 from tlob130ext; |
| -- following should return error since only external lobs will be allowed |
| update tlob130ext set c4=stringtolob('gggg'); |
| -- following should work. |
| update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt'); |
| delete from tlob130ext; |
| |
| -- test to ensure all lob dependent tables and schemas containing lob tables |
| -- get dropped cleanly. |
| |
| ?section lob_drop_table_schema |
| log; |
| cleanup schema trafodion.lobsch; |
| log LOG130; |
| create schema trafodion.lobsch; |
| set schema trafodion.lobsch; |
| create table tlob130ts1 (c1 int not null, c2 blob, primary key (c1)); |
| create table tlob130ts2 (c1 int not null, c2 blob, primary key (c1)); |
| drop table tlob130ts1; |
| get tables; |
| drop schema trafodion.lobsch cascade; |
| select OBJECT_NAME,OBJECT_TYPE from TRAFODION."_MD_".OBJECTS where catalog_name='TRAFODION' AND schema_name='LOBSCH'; |
| --go back to the schema used for the rest of the tests. |
| set schema trafodion.lob130; |
| |
| |
| -- cleanup |
| ?section lob_general_cleanup |
| log; |
| sh rm t130_*; |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130* |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_a1.txt |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_b1.txt |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_c1.txt |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_d1.txt |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_e1.txt |
| sh regrhadoop.ksh fs -rm /user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.txt; |
| sh rm lobinput_*; |
| sh rm deep.jpg; |
| sh rm anoush.jpg; |
| drop table tlob130txt1; |
| drop table tlob130bin1; |
| drop table tlob130txt_limit50; |
| drop table tlob130bin_limit1K; |
| sh rm TMP130; |
| sh rm tlob130txt1; |
| |
| sh rm tlob130_txt*.txt; |
| sh rm tlob130_deep*.jpg; |
| sh rm tlob130_anoush*.jpg; |
| drop table tlob130txt3; |
| drop table tlob130bin3; |
| drop table tlob130bt; |
| drop table tlob130txt2; |
| drop table tlob130bin2; |
| drop table tlob130ext; |
| drop table tlob130ext2; |
| sh rm TMP130; |
| sh rm tlob130txt2; |
| sh rm lobc2out.jpg; |
| sh rm lobc4ext.txt; |
| sh rm TEST130_argfile*; |
| drop table tlob130gc; |
| drop table tlob130gt; |
| drop table tlob130t5; |
| drop table tlob130_not_external; |
| drop external table if exists date_dim for hive.hive.date_dim; |
| drop schema trafodion.lob130 cascade; |
| |
| |
| exit; |
| |
| |
| |
| |
| |