| drop table if exists table1_n1; |
| drop table if exists table1_1; |
| |
| dfs -cp ${system:hive.root}data/files/table1.avsc ${system:test.tmp.dir}/avrotblsjoin.avsc; |
| dfs -cp ${system:hive.root}data/files/table1_1.avsc ${system:test.tmp.dir}/avrotblsjoin_1.avsc; |
| |
| create table table1_n1 |
| ROW FORMAT SERDE |
| 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' |
| STORED AS INPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' |
| OUTPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' |
| TBLPROPERTIES ('avro.schema.url'='${system:test.tmp.dir}/avrotblsjoin.avsc'); |
| create table table1_1 |
| ROW FORMAT SERDE |
| 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' |
| STORED AS INPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' |
| OUTPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' |
| TBLPROPERTIES ('avro.schema.url'='${system:test.tmp.dir}/avrotblsjoin_1.avsc'); |
| insert into table1_n1 values ("1", "2", "3"); |
| insert into table1_1 values (1, "2"); |
| set hive.auto.convert.join=false; |
| set hive.strict.checks.type.safety=false; |
| set hive.mapred.mode=nonstrict; |
| select table1_n1.col1, table1_1.* from table1_n1 join table1_1 on table1_n1.col1=table1_1.col1 where table1_1.col1="1"; |
| |
| dfs -rm ${system:test.tmp.dir}/avrotblsjoin.avsc; |
| dfs -rm ${system:test.tmp.dir}/avrotblsjoin_1.avsc; |