@Test
public void testBetween2() throws IOException { // for TAJO-249
Schema schema3 = new Schema();
schema3.addColumn("date_a", INT4);
schema3.addColumn("date_b", INT4);
schema3.addColumn("date_c", INT4);
schema3.addColumn("date_d", INT4);
String query =
"select "
+ "case "
+ "when date_a BETWEEN 20130705 AND 20130715 AND ((date_b BETWEEN 20100101 AND 20120601) OR date_b > 20130715) "
+ "AND (date_c < 20120601 OR date_c > 20130715) AND date_d > 20130715"
+ "then 1 else 0 end from table1";
testEval(schema3, "table1", "20130715,20100102,20120525,20130716", query, new String[] {"1"});
testEval(schema3, "table1", "20130716,20100102,20120525,20130716", query, new String[] {"0"});
// date_b
testEval(schema3, "table1", "20130715,20100102,20120525,20130716", query, new String[] {"1"});
testEval(schema3, "table1", "20130715,20120602,20120525,20130716", query, new String[] {"0"});
testEval(schema3, "table1", "20130715,20091201,20120525,20130716", query, new String[] {"0"});
testEval(schema3, "table1", "20130715,20130716,20120525,20130716", query, new String[] {"1"});
// date_c
testEval(schema3, "table1", "20130715,20100102,20120525,20130716", query, new String[] {"1"});
testEval(schema3, "table1", "20130715,20100102,20120602,20130716", query, new String[] {"0"});
testEval(schema3, "table1", "20130715,20100102,20130716,20130716", query, new String[] {"1"});
testEval(schema3, "table1", "20130715,20100102,20130714,20130716", query, new String[] {"0"});
// date_d
testEval(schema3, "table1", "20130715,20100102,20120525,20130716", query, new String[] {"1"});
testEval(schema3, "table1", "20130715,20100102,20120525,20130705", query, new String[] {"0"});
}
@Test
public void testInPredicate() throws IOException {
Schema schema2 = new Schema();
schema2.addColumn("col1", TEXT);
schema2.addColumn("col2", TEXT);
schema2.addColumn("col3", TEXT);
testEval(
schema2,
"table1",
"a,b,c",
"select col1 in ('a'), col2 in ('a', 'c') from table1",
new String[] {"t", "f"});
testEval(
schema2,
"table1",
"a,,c",
"select col1 in ('a','b','c'), (col2 in ('a', 'c')) is null from table1",
new String[] {"t", "t"});
testEval(
schema2,
"table1",
"2014-03-21,2015-04-01,2016-04-01",
"select substr(col1,1,4) in ('2014','2015','2016'), substr(col1,6,2)::int4 in (1,2,3) from table1",
new String[] {"t", "t"});
// null handling test
testEval(
schema2,
"table1",
"2014-03-21,,2015-04-01",
"select (substr(col2,1,4)::int4 in (2014,2015,2016)) is null from table1",
new String[] {"t"});
}
@Test
public final void testComparatorsFromJoinQual() {
Schema outerSchema = new Schema();
outerSchema.addColumn("employee.id1", CatalogUtil.newSimpleDataType(Type.INT4));
outerSchema.addColumn("employee.id2", CatalogUtil.newSimpleDataType(Type.INT4));
Schema innerSchema = new Schema();
innerSchema.addColumn("people.fid1", CatalogUtil.newSimpleDataType(Type.INT4));
innerSchema.addColumn("people.fid2", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f1 = new FieldEval("employee.id1", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f2 = new FieldEval("people.fid1", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f3 = new FieldEval("employee.id2", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f4 = new FieldEval("people.fid2", CatalogUtil.newSimpleDataType(Type.INT4));
EvalNode joinQual = new BinaryEval(EvalType.EQUAL, f1, f2);
TupleComparator[] comparators =
PlannerUtil.getComparatorsFromJoinQual(joinQual, outerSchema, innerSchema);
Tuple t1 = new VTuple(2);
t1.put(0, DatumFactory.createInt4(1));
t1.put(1, DatumFactory.createInt4(2));
Tuple t2 = new VTuple(2);
t2.put(0, DatumFactory.createInt4(2));
t2.put(1, DatumFactory.createInt4(3));
TupleComparator outerComparator = comparators[0];
assertTrue(outerComparator.compare(t1, t2) < 0);
assertTrue(outerComparator.compare(t2, t1) > 0);
TupleComparator innerComparator = comparators[1];
assertTrue(innerComparator.compare(t1, t2) < 0);
assertTrue(innerComparator.compare(t2, t1) > 0);
// tests for composited join key
EvalNode joinQual2 = new BinaryEval(EvalType.EQUAL, f3, f4);
EvalNode compositedJoinQual = new BinaryEval(EvalType.AND, joinQual, joinQual2);
comparators =
PlannerUtil.getComparatorsFromJoinQual(compositedJoinQual, outerSchema, innerSchema);
outerComparator = comparators[0];
assertTrue(outerComparator.compare(t1, t2) < 0);
assertTrue(outerComparator.compare(t2, t1) > 0);
innerComparator = comparators[1];
assertTrue(innerComparator.compare(t1, t2) < 0);
assertTrue(innerComparator.compare(t2, t1) > 0);
}
public void execExplain(
Session session,
String query,
LogicalPlan plan,
QueryContext queryContext,
boolean isGlobal,
SubmitQueryResponse.Builder response)
throws Exception {
String explainStr;
boolean isTest = queryContext.getBool(SessionVars.TEST_PLAN_SHAPE_FIX_ENABLED);
if (isTest) {
ExplainPlanPreprocessorForTest preprocessorForTest = new ExplainPlanPreprocessorForTest();
preprocessorForTest.prepareTest(plan);
}
if (isGlobal) {
GlobalPlanner planner = new GlobalPlanner(context.getConf(), context.getCatalog());
MasterPlan masterPlan = compileMasterPlan(plan, queryContext, planner);
if (isTest) {
ExplainGlobalPlanPreprocessorForTest globalPlanPreprocessorForTest =
new ExplainGlobalPlanPreprocessorForTest();
globalPlanPreprocessorForTest.prepareTest(masterPlan);
}
explainStr = masterPlan.toString();
} else {
explainStr = PlannerUtil.buildExplainString(plan.getRootBlock().getRoot());
}
Schema schema = new Schema();
schema.addColumn("explain", TajoDataTypes.Type.TEXT);
RowStoreUtil.RowStoreEncoder encoder = RowStoreUtil.createEncoder(schema);
SerializedResultSet.Builder serializedResBuilder = SerializedResultSet.newBuilder();
VTuple tuple = new VTuple(1);
String[] lines = explainStr.split("\n");
int bytesNum = 0;
for (String line : lines) {
tuple.put(0, DatumFactory.createText(line));
byte[] encodedData = encoder.toBytes(tuple);
bytesNum += encodedData.length;
serializedResBuilder.addSerializedTuples(ByteString.copyFrom(encodedData));
}
serializedResBuilder.setSchema(schema.getProto());
serializedResBuilder.setBytesNum(bytesNum);
QueryInfo queryInfo =
context
.getQueryJobManager()
.createNewSimpleQuery(
queryContext, session, query, (LogicalRootNode) plan.getRootBlock().getRoot());
response.setState(OK);
response.setQueryId(queryInfo.getQueryId().getProto());
response.setResultType(ResultType.ENCLOSED);
response.setResultSet(serializedResBuilder.build());
response.setMaxRowNum(lines.length);
}
@Test
public final void testGetJoinKeyPairs() {
Schema outerSchema = new Schema();
outerSchema.addColumn("employee.id1", CatalogUtil.newSimpleDataType(Type.INT4));
outerSchema.addColumn("employee.id2", CatalogUtil.newSimpleDataType(Type.INT4));
Schema innerSchema = new Schema();
innerSchema.addColumn("people.fid1", CatalogUtil.newSimpleDataType(Type.INT4));
innerSchema.addColumn("people.fid2", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f1 = new FieldEval("employee.id1", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f2 = new FieldEval("people.fid1", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f3 = new FieldEval("employee.id2", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f4 = new FieldEval("people.fid2", CatalogUtil.newSimpleDataType(Type.INT4));
EvalNode joinQual = new BinaryEval(EvalType.EQUAL, f1, f2);
// the case where part is the outer and partsupp is the inner.
List<Column[]> pairs = PlannerUtil.getJoinKeyPairs(joinQual, outerSchema, innerSchema);
assertEquals(1, pairs.size());
assertEquals("employee.id1", pairs.get(0)[0].getQualifiedName());
assertEquals("people.fid1", pairs.get(0)[1].getQualifiedName());
// after exchange of outer and inner
pairs = PlannerUtil.getJoinKeyPairs(joinQual, innerSchema, outerSchema);
assertEquals("people.fid1", pairs.get(0)[0].getQualifiedName());
assertEquals("employee.id1", pairs.get(0)[1].getQualifiedName());
// composited join key test
EvalNode joinQual2 = new BinaryEval(EvalType.EQUAL, f3, f4);
EvalNode compositedJoinQual = new BinaryEval(EvalType.AND, joinQual, joinQual2);
pairs = PlannerUtil.getJoinKeyPairs(compositedJoinQual, outerSchema, innerSchema);
assertEquals(2, pairs.size());
assertEquals("employee.id1", pairs.get(0)[0].getQualifiedName());
assertEquals("people.fid1", pairs.get(0)[1].getQualifiedName());
assertEquals("employee.id2", pairs.get(1)[0].getQualifiedName());
assertEquals("people.fid2", pairs.get(1)[1].getQualifiedName());
// after exchange of outer and inner
pairs = PlannerUtil.getJoinKeyPairs(compositedJoinQual, innerSchema, outerSchema);
assertEquals(2, pairs.size());
assertEquals("people.fid1", pairs.get(0)[0].getQualifiedName());
assertEquals("employee.id1", pairs.get(0)[1].getQualifiedName());
assertEquals("people.fid2", pairs.get(1)[0].getQualifiedName());
assertEquals("employee.id2", pairs.get(1)[1].getQualifiedName());
}
@Before
public void setUp() throws Exception {
this.conf = new TajoConf();
util = new TajoTestingCluster();
catalog = util.startCatalogCluster().getCatalog();
testDir = CommonTestingUtil.getTestDir(TEST_PATH);
conf.setVar(TajoConf.ConfVars.WORKER_TEMPORAL_DIR, testDir.toString());
sm = StorageManagerFactory.getStorageManager(conf, testDir);
Schema schema = new Schema();
schema.addColumn("managerId", Type.INT4);
schema.addColumn("empId", Type.INT4);
schema.addColumn("deptName", Type.TEXT);
TableMeta employeeMeta = CatalogUtil.newTableMeta(StoreType.CSV);
Path employeePath = new Path(testDir, "employee.csv");
Appender appender =
StorageManagerFactory.getStorageManager(conf)
.getAppender(employeeMeta, schema, employeePath);
appender.enableStats();
appender.init();
Tuple tuple = new VTuple(schema.getColumnNum());
for (int i = 0; i < numTuple; i++) {
tuple.put(
new Datum[] {
DatumFactory.createInt4(rnd.nextInt(50)),
DatumFactory.createInt4(rnd.nextInt(100)),
DatumFactory.createText("dept_" + i),
});
appender.addTuple(tuple);
}
appender.flush();
appender.close();
System.out.println(
appender.getStats().getNumRows()
+ " rows ("
+ (appender.getStats().getNumBytes() / 1048576)
+ " MB)");
employee = new TableDesc("employee", schema, employeeMeta, employeePath);
catalog.addTable(employee);
analyzer = new SQLAnalyzer();
planner = new LogicalPlanner(catalog);
}
@Test
public void testComparisonGreaterThanEqual() throws IOException {
Schema schema1 = new Schema();
schema1.addColumn("col1", INT4);
schema1.addColumn("col2", INT4);
schema1.addColumn("col3", INT4);
schema1.addColumn("col4", INT4);
testEval(
schema1,
"table1",
"123,123,456,-123",
"select col1 >= col2, col3 >= col2, col1 >= col4 from table1",
new String[] {"t", "t", "t"});
testEval(
schema1,
"table1",
"123,456,,",
"select col2 >= col1, col1 >= col2, (col1 >= col3) is null, (col4 >= col1) is null from table1",
new String[] {"t", "f", "t", "t"});
}
@Test
public void testComparisonNotEqual() throws IOException {
Schema schema1 = new Schema();
schema1.addColumn("col1", INT4);
schema1.addColumn("col2", INT4);
schema1.addColumn("col3", INT4);
schema1.addColumn("col4", INT4);
testEval(
schema1,
"table1",
"123,123,456,-123",
"select col1 <> col2, col1 <> col3, col1 <> col4 from table1",
new String[] {"f", "t", "t"});
testEval(
schema1,
"table1",
"123,123,,",
"select col1 <> col2, (col1 <> col3) is null, (col3 <> col2) is null from table1",
new String[] {"f", "t", "t"});
}
static {
schema.addColumn("col1", Type.BOOLEAN);
schema.addColumn("col2", Type.CHAR, 7);
schema.addColumn("col3", Type.INT2);
schema.addColumn("col4", Type.INT4);
schema.addColumn("col5", Type.INT8);
schema.addColumn("col6", Type.FLOAT4);
schema.addColumn("col7", Type.FLOAT8);
schema.addColumn("col8", Type.TEXT);
schema.addColumn("col9", Type.BLOB);
schema.addColumn("col10", Type.INET4);
baseTuple =
new VTuple(
new Datum[] {
DatumFactory.createBool(true), // 0
DatumFactory.createChar("hyunsik"), // 1
DatumFactory.createInt2((short) 17), // 2
DatumFactory.createInt4(59), // 3
DatumFactory.createInt8(23l), // 4
DatumFactory.createFloat4(77.9f), // 5
DatumFactory.createFloat8(271.9d), // 6
DatumFactory.createText("hyunsik"), // 7
DatumFactory.createBlob("hyunsik".getBytes()), // 8
DatumFactory.createInet4("192.168.0.1"), // 9
});
}
@Test
public void testNot() throws IOException {
testSimpleEval("select true;", new String[] {"t"});
testSimpleEval("select not true;", new String[] {"f"});
testSimpleEval("select (true);", new String[] {"t"});
testSimpleEval("select not (true);", new String[] {"f"});
testSimpleEval("select not (not (true));", new String[] {"t"});
testSimpleEval("select (not (1 > null)) is null;", new String[] {"t"});
Schema schema1 = new Schema();
schema1.addColumn("col1", INT4);
schema1.addColumn("col2", INT4);
schema1.addColumn("col3", INT4);
testEval(
schema1,
"table1",
"123,123,456,-123",
"select col1 = col2, col1 = col3 from table1",
new String[] {"t", "f"});
}
@Test
public final void testGetSortKeysFromJoinQual() {
Schema outerSchema = new Schema();
outerSchema.addColumn("employee.id1", CatalogUtil.newSimpleDataType(Type.INT4));
outerSchema.addColumn("employee.id2", CatalogUtil.newSimpleDataType(Type.INT4));
Schema innerSchema = new Schema();
innerSchema.addColumn("people.fid1", CatalogUtil.newSimpleDataType(Type.INT4));
innerSchema.addColumn("people.fid2", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f1 = new FieldEval("employee.id1", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f2 = new FieldEval("people.fid1", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f3 = new FieldEval("employee.id2", CatalogUtil.newSimpleDataType(Type.INT4));
FieldEval f4 = new FieldEval("people.fid2", CatalogUtil.newSimpleDataType(Type.INT4));
EvalNode joinQual = new BinaryEval(EvalType.EQUAL, f1, f2);
SortSpec[][] sortSpecs =
PlannerUtil.getSortKeysFromJoinQual(joinQual, outerSchema, innerSchema);
assertEquals(2, sortSpecs.length);
assertEquals(1, sortSpecs[0].length);
assertEquals(1, sortSpecs[1].length);
assertEquals(outerSchema.getColumn("id1"), sortSpecs[0][0].getSortKey());
assertEquals(innerSchema.getColumn("fid1"), sortSpecs[1][0].getSortKey());
// tests for composited join key
EvalNode joinQual2 = new BinaryEval(EvalType.EQUAL, f3, f4);
EvalNode compositedJoinQual = new BinaryEval(EvalType.AND, joinQual, joinQual2);
sortSpecs = PlannerUtil.getSortKeysFromJoinQual(compositedJoinQual, outerSchema, innerSchema);
assertEquals(2, sortSpecs.length);
assertEquals(2, sortSpecs[0].length);
assertEquals(2, sortSpecs[1].length);
assertEquals(outerSchema.getColumn("id1"), sortSpecs[0][0].getSortKey());
assertEquals(outerSchema.getColumn("id2"), sortSpecs[0][1].getSortKey());
assertEquals(innerSchema.getColumn("fid1"), sortSpecs[1][0].getSortKey());
assertEquals(innerSchema.getColumn("fid2"), sortSpecs[1][1].getSortKey());
}
@Test
public void testIsNullPredicate() throws IOException {
Schema schema1 = new Schema();
schema1.addColumn("col1", INT4);
schema1.addColumn("col2", INT4);
testEval(
schema1,
"table1",
"123,",
"select col1 is null, col2 is null as a from table1",
new String[] {"f", "t"});
testEval(
schema1,
"table1",
"123,",
"select col1 is not null, col2 is not null as a from table1",
new String[] {"t", "f"});
}
@Test
public void testIsNullPredicateWithFunction() throws IOException {
Schema schema2 = new Schema();
schema2.addColumn("col1", TEXT);
schema2.addColumn("col2", TEXT);
testEval(
schema2,
"table1",
"_123,",
"select ltrim(col1, '_') is null, upper(col2) is null as a from table1",
new String[] {"f", "t"});
testEval(
schema2,
"table1",
"_123,",
"select ltrim(col1, '_') is not null, upper(col2) is not null as a from table1",
new String[] {"t", "f"});
}
@BeforeClass
public static void setUp() throws Exception {
util = new TajoTestingCluster();
util.startCatalogCluster();
catalog = util.getMiniCatalogCluster().getCatalog();
Schema schema = new Schema();
schema.addColumn("name", Type.TEXT);
schema.addColumn("empId", CatalogUtil.newSimpleDataType(Type.INT4));
schema.addColumn("deptName", Type.TEXT);
Schema schema2 = new Schema();
schema2.addColumn("deptName", Type.TEXT);
schema2.addColumn("manager", Type.TEXT);
Schema schema3 = new Schema();
schema3.addColumn("deptName", Type.TEXT);
schema3.addColumn("score", CatalogUtil.newSimpleDataType(Type.INT4));
TableMeta meta = CatalogUtil.newTableMeta(StoreType.CSV);
TableDesc people = new TableDesc("employee", schema, meta, CommonTestingUtil.getTestDir());
catalog.addTable(people);
TableDesc student =
new TableDesc(
"dept", schema2, StoreType.CSV, new Options(), CommonTestingUtil.getTestDir());
catalog.addTable(student);
TableDesc score =
new TableDesc(
"score", schema3, StoreType.CSV, new Options(), CommonTestingUtil.getTestDir());
catalog.addTable(score);
FunctionDesc funcDesc =
new FunctionDesc(
"sumtest",
SumInt.class,
FunctionType.AGGREGATION,
CatalogUtil.newSimpleDataType(Type.INT4),
CatalogUtil.newSimpleDataTypeArray(Type.INT4));
catalog.createFunction(funcDesc);
analyzer = new SQLAnalyzer();
planner = new LogicalPlanner(catalog);
}
@Test
public void testBooleanTestOnTable() throws IOException {
Schema schema = new Schema();
schema.addColumn("col1", BOOLEAN);
schema.addColumn("col2", BOOLEAN);
testEval(
schema,
"table1",
"t,f",
"select col1 is true, col2 is false from table1",
new String[] {"t", "t"});
testEval(
schema,
"table1",
"t,f",
"select col1 is not true, col2 is not false from table1",
new String[] {"f", "f"});
testEval(
schema,
"table1",
"t,f",
"select not col1 is not true, not col2 is not false from table1",
new String[] {"t", "t"});
}
@Before
public void setUp() throws Exception {
util = new TajoTestingCluster();
util.initTestDir();
util.startCatalogCluster();
catalog = util.getCatalogService();
testDir = CommonTestingUtil.getTestDir(TEST_PATH);
catalog.createTablespace(DEFAULT_TABLESPACE_NAME, testDir.toUri().toString());
catalog.createDatabase(DEFAULT_DATABASE_NAME, DEFAULT_TABLESPACE_NAME);
conf = util.getConfiguration();
// ----------------- dep3 ------------------------------
// dep_id | dep_name | loc_id
// --------------------------------
// 0 | dep_0 | 1000
// 1 | dep_1 | 1001
// 2 | dep_2 | 1002
// 3 | dep_3 | 1003
// 4 | dep_4 | 1004
// 5 | dep_5 | 1005
// 6 | dep_6 | 1006
// 7 | dep_7 | 1007
// 8 | dep_8 | 1008
// 9 | dep_9 | 1009
Schema dep3Schema = new Schema();
dep3Schema.addColumn("dep_id", Type.INT4);
dep3Schema.addColumn("dep_name", Type.TEXT);
dep3Schema.addColumn("loc_id", Type.INT4);
TableMeta dep3Meta = CatalogUtil.newTableMeta("TEXT");
Path dep3Path = new Path(testDir, "dep3.csv");
Appender appender1 =
((FileTablespace) TablespaceManager.getLocalFs())
.getAppender(dep3Meta, dep3Schema, dep3Path);
appender1.init();
VTuple tuple = new VTuple(dep3Schema.size());
for (int i = 0; i < 10; i++) {
tuple.put(
new Datum[] {
DatumFactory.createInt4(i),
DatumFactory.createText("dept_" + i),
DatumFactory.createInt4(1000 + i)
});
appender1.addTuple(tuple);
}
appender1.flush();
appender1.close();
dep3 = CatalogUtil.newTableDesc(DEP3_NAME, dep3Schema, dep3Meta, dep3Path);
catalog.createTable(dep3);
// ----------------- dep4 ------------------------------
// dep_id | dep_name | loc_id
// --------------------------------
// 0 | dep_0 | 1000
// 1 | dep_1 | 1001
// 2 | dep_2 | 1002
// 3 | dep_3 | 1003
// 4 | dep_4 | 1004
// 5 | dep_5 | 1005
// 6 | dep_6 | 1006
// 7 | dep_7 | 1007
// 8 | dep_8 | 1008
// 9 | dep_9 | 1009
// 10 | dep_10 | 1010
Schema dep4Schema = new Schema();
dep4Schema.addColumn("dep_id", Type.INT4);
dep4Schema.addColumn("dep_name", Type.TEXT);
dep4Schema.addColumn("loc_id", Type.INT4);
TableMeta dep4Meta = CatalogUtil.newTableMeta("TEXT");
Path dep4Path = new Path(testDir, "dep4.csv");
Appender appender4 =
((FileTablespace) TablespaceManager.getLocalFs())
.getAppender(dep4Meta, dep4Schema, dep4Path);
appender4.init();
VTuple tuple4 = new VTuple(dep4Schema.size());
for (int i = 0; i < 11; i++) {
tuple4.put(
new Datum[] {
DatumFactory.createInt4(i),
DatumFactory.createText("dept_" + i),
DatumFactory.createInt4(1000 + i)
});
appender4.addTuple(tuple4);
}
appender4.flush();
appender4.close();
dep4 = CatalogUtil.newTableDesc(DEP4_NAME, dep4Schema, dep4Meta, dep4Path);
catalog.createTable(dep4);
// ----------------- job3 ------------------------------
// job_id | job_title
// ----------------------
// 101 | job_101
// 102 | job_102
// 103 | job_103
Schema job3Schema = new Schema();
job3Schema.addColumn("job_id", Type.INT4);
job3Schema.addColumn("job_title", Type.TEXT);
TableMeta job3Meta = CatalogUtil.newTableMeta("TEXT");
Path job3Path = new Path(testDir, "job3.csv");
Appender appender2 =
((FileTablespace) TablespaceManager.getLocalFs())
.getAppender(job3Meta, job3Schema, job3Path);
appender2.init();
VTuple tuple2 = new VTuple(job3Schema.size());
for (int i = 1; i < 4; i++) {
int x = 100 + i;
tuple2.put(
new Datum[] {DatumFactory.createInt4(100 + i), DatumFactory.createText("job_" + x)});
appender2.addTuple(tuple2);
}
appender2.flush();
appender2.close();
job3 = CatalogUtil.newTableDesc(JOB3_NAME, job3Schema, job3Meta, job3Path);
catalog.createTable(job3);
// ---------------------emp3 --------------------
// emp_id | first_name | last_name | dep_id | salary | job_id
// ------------------------------------------------------------
// 11 | fn_11 | ln_11 | 1 | 123 | 101
// 13 | fn_13 | ln_13 | 3 | 369 | 103
// 15 | fn_15 | ln_15 | 5 | 615 | null
// 17 | fn_17 | ln_17 | 7 | 861 | null
// 19 | fn_19 | ln_19 | 9 | 1107 | null
// 21 | fn_21 | ln_21 | 1 | 123 | 101
// 23 | fn_23 | ln_23 | 3 | 369 | 103
Schema emp3Schema = new Schema();
emp3Schema.addColumn("emp_id", Type.INT4);
emp3Schema.addColumn("first_name", Type.TEXT);
emp3Schema.addColumn("last_name", Type.TEXT);
emp3Schema.addColumn("dep_id", Type.INT4);
emp3Schema.addColumn("salary", Type.FLOAT4);
emp3Schema.addColumn("job_id", Type.INT4);
TableMeta emp3Meta = CatalogUtil.newTableMeta("TEXT");
Path emp3Path = new Path(testDir, "emp3.csv");
Appender appender3 =
((FileTablespace) TablespaceManager.getLocalFs())
.getAppender(emp3Meta, emp3Schema, emp3Path);
appender3.init();
VTuple tuple3 = new VTuple(emp3Schema.size());
for (int i = 1; i < 4; i += 2) {
int x = 10 + i;
tuple3.put(
new Datum[] {
DatumFactory.createInt4(10 + i),
DatumFactory.createText("firstname_" + x),
DatumFactory.createText("lastname_" + x),
DatumFactory.createInt4(i),
DatumFactory.createFloat4(123 * i),
DatumFactory.createInt4(100 + i)
});
appender3.addTuple(tuple3);
int y = 20 + i;
tuple3.put(
new Datum[] {
DatumFactory.createInt4(20 + i),
DatumFactory.createText("firstname_" + y),
DatumFactory.createText("lastname_" + y),
DatumFactory.createInt4(i),
DatumFactory.createFloat4(123 * i),
DatumFactory.createInt4(100 + i)
});
appender3.addTuple(tuple3);
}
for (int i = 5; i < 10; i += 2) {
int x = 10 + i;
tuple3.put(
new Datum[] {
DatumFactory.createInt4(10 + i),
DatumFactory.createText("firstname_" + x),
DatumFactory.createText("lastname_" + x),
DatumFactory.createInt4(i),
DatumFactory.createFloat4(123 * i),
DatumFactory.createNullDatum()
});
appender3.addTuple(tuple3);
}
appender3.flush();
appender3.close();
emp3 = CatalogUtil.newTableDesc(EMP3_NAME, emp3Schema, emp3Meta, emp3Path);
catalog.createTable(emp3);
// ---------------------phone3 --------------------
// emp_id | phone_number
// -----------------------------------------------
// this table is empty, no rows
Schema phone3Schema = new Schema();
phone3Schema.addColumn("emp_id", Type.INT4);
phone3Schema.addColumn("phone_number", Type.TEXT);
TableMeta phone3Meta = CatalogUtil.newTableMeta("TEXT");
Path phone3Path = new Path(testDir, "phone3.csv");
Appender appender5 =
((FileTablespace) TablespaceManager.getLocalFs())
.getAppender(phone3Meta, phone3Schema, phone3Path);
appender5.init();
appender5.flush();
appender5.close();
phone3 = CatalogUtil.newTableDesc(PHONE3_NAME, phone3Schema, phone3Meta, phone3Path);
catalog.createTable(phone3);
analyzer = new SQLAnalyzer();
planner = new LogicalPlanner(catalog, TablespaceManager.getInstance());
defaultContext = LocalTajoTestingUtility.createDummyContext(conf);
}
@Override
public final CatalogProtos.TableDescProto getTable(String databaseName, final String tableName)
throws CatalogException {
org.apache.hadoop.hive.ql.metadata.Table table = null;
HiveCatalogStoreClientPool.HiveCatalogStoreClient client = null;
Path path = null;
String storeType = null;
org.apache.tajo.catalog.Schema schema = null;
KeyValueSet options = null;
TableStats stats = null;
PartitionMethodDesc partitions = null;
//////////////////////////////////
// set tajo table schema.
//////////////////////////////////
try {
// get hive table schema
try {
client = clientPool.getClient();
table = HiveCatalogUtil.getTable(client.getHiveClient(), databaseName, tableName);
path = table.getPath();
} catch (NoSuchObjectException nsoe) {
throw new UndefinedTableException(tableName);
} catch (Exception e) {
throw new TajoInternalError(e);
}
// convert HiveCatalogStore field schema into tajo field schema.
schema = new org.apache.tajo.catalog.Schema();
List<FieldSchema> fieldSchemaList = table.getCols();
boolean isPartitionKey = false;
for (FieldSchema eachField : fieldSchemaList) {
isPartitionKey = false;
if (table.getPartitionKeys() != null) {
for (FieldSchema partitionKey : table.getPartitionKeys()) {
if (partitionKey.getName().equals(eachField.getName())) {
isPartitionKey = true;
}
}
}
if (!isPartitionKey) {
String fieldName =
databaseName
+ CatalogConstants.IDENTIFIER_DELIMITER
+ tableName
+ CatalogConstants.IDENTIFIER_DELIMITER
+ eachField.getName();
TajoDataTypes.Type dataType =
HiveCatalogUtil.getTajoFieldType(eachField.getType().toString());
schema.addColumn(fieldName, dataType);
}
}
// validate field schema.
HiveCatalogUtil.validateSchema(table);
stats = new TableStats();
options = new KeyValueSet();
options.putAll(table.getParameters());
options.remove("EXTERNAL");
Properties properties = table.getMetadata();
if (properties != null) {
// set field delimiter
String fieldDelimiter = "", nullFormat = "";
if (properties.getProperty(serdeConstants.FIELD_DELIM) != null) {
fieldDelimiter = properties.getProperty(serdeConstants.FIELD_DELIM);
} else {
// if hive table used default row format delimiter, Properties doesn't have it.
// So, Tajo must set as follows:
fieldDelimiter = "\u0001";
}
// set null format
if (properties.getProperty(serdeConstants.SERIALIZATION_NULL_FORMAT) != null) {
nullFormat = properties.getProperty(serdeConstants.SERIALIZATION_NULL_FORMAT);
} else {
nullFormat = "\\N";
}
options.remove(serdeConstants.SERIALIZATION_NULL_FORMAT);
// set file output format
String fileOutputformat =
properties.getProperty(hive_metastoreConstants.FILE_OUTPUT_FORMAT);
storeType = HiveCatalogUtil.getStoreType(fileOutputformat);
if (storeType.equalsIgnoreCase("TEXT")) {
options.set(
StorageConstants.TEXT_DELIMITER, StringEscapeUtils.escapeJava(fieldDelimiter));
options.set(StorageConstants.TEXT_NULL, StringEscapeUtils.escapeJava(nullFormat));
} else if (storeType.equals("RCFILE")) {
options.set(StorageConstants.RCFILE_NULL, StringEscapeUtils.escapeJava(nullFormat));
String serde = properties.getProperty(serdeConstants.SERIALIZATION_LIB);
if (LazyBinaryColumnarSerDe.class.getName().equals(serde)) {
options.set(StorageConstants.RCFILE_SERDE, StorageConstants.DEFAULT_BINARY_SERDE);
} else if (ColumnarSerDe.class.getName().equals(serde)) {
options.set(StorageConstants.RCFILE_SERDE, StorageConstants.DEFAULT_TEXT_SERDE);
}
} else if (storeType.equals("SEQUENCEFILE")) {
options.set(
StorageConstants.SEQUENCEFILE_DELIMITER,
StringEscapeUtils.escapeJava(fieldDelimiter));
options.set(StorageConstants.SEQUENCEFILE_NULL, StringEscapeUtils.escapeJava(nullFormat));
String serde = properties.getProperty(serdeConstants.SERIALIZATION_LIB);
if (LazyBinarySerDe.class.getName().equals(serde)) {
options.set(StorageConstants.SEQUENCEFILE_SERDE, StorageConstants.DEFAULT_BINARY_SERDE);
} else if (LazySimpleSerDe.class.getName().equals(serde)) {
options.set(StorageConstants.SEQUENCEFILE_SERDE, StorageConstants.DEFAULT_TEXT_SERDE);
}
}
// set data size
long totalSize = 0;
if (properties.getProperty("totalSize") != null) {
totalSize = Long.parseLong(properties.getProperty("totalSize"));
} else {
try {
FileSystem fs = path.getFileSystem(conf);
if (fs.exists(path)) {
totalSize = fs.getContentSummary(path).getLength();
}
} catch (IOException ioe) {
throw new TajoInternalError(ioe);
}
}
stats.setNumBytes(totalSize);
}
// set partition keys
List<FieldSchema> partitionKeys = table.getPartitionKeys();
if (null != partitionKeys) {
org.apache.tajo.catalog.Schema expressionSchema = new org.apache.tajo.catalog.Schema();
StringBuilder sb = new StringBuilder();
if (partitionKeys.size() > 0) {
for (int i = 0; i < partitionKeys.size(); i++) {
FieldSchema fieldSchema = partitionKeys.get(i);
TajoDataTypes.Type dataType =
HiveCatalogUtil.getTajoFieldType(fieldSchema.getType().toString());
String fieldName =
databaseName
+ CatalogConstants.IDENTIFIER_DELIMITER
+ tableName
+ CatalogConstants.IDENTIFIER_DELIMITER
+ fieldSchema.getName();
expressionSchema.addColumn(new Column(fieldName, dataType));
if (i > 0) {
sb.append(",");
}
sb.append(fieldSchema.getName());
}
partitions =
new PartitionMethodDesc(
databaseName, tableName, PartitionType.COLUMN, sb.toString(), expressionSchema);
}
}
} finally {
if (client != null) client.release();
}
TableMeta meta = new TableMeta(storeType, options);
TableDesc tableDesc = new TableDesc(databaseName + "." + tableName, schema, meta, path.toUri());
if (table.getTableType().equals(TableType.EXTERNAL_TABLE)) {
tableDesc.setExternal(true);
}
if (stats != null) {
tableDesc.setStats(stats);
}
if (partitions != null) {
tableDesc.setPartitionMethod(partitions);
}
return tableDesc.getProto();
}
@Before
public void setup() throws Exception {
this.randomValues = new HashMap<Integer, Integer>();
this.conf = new TajoConf();
util = new TajoTestingCluster();
util.startCatalogCluster();
catalog = util.getMiniCatalogCluster().getCatalog();
Path workDir = CommonTestingUtil.getTestDir();
catalog.createTablespace(DEFAULT_TABLESPACE_NAME, workDir.toUri().toString());
catalog.createDatabase(TajoConstants.DEFAULT_DATABASE_NAME, DEFAULT_TABLESPACE_NAME);
sm = StorageManagerFactory.getStorageManager(conf, workDir);
idxPath = new Path(workDir, "test.idx");
Schema schema = new Schema();
schema.addColumn("managerid", Type.INT4);
schema.addColumn("empid", Type.INT4);
schema.addColumn("deptname", Type.TEXT);
this.idxSchema = new Schema();
idxSchema.addColumn("managerid", Type.INT4);
SortSpec[] sortKeys = new SortSpec[1];
sortKeys[0] = new SortSpec(idxSchema.getColumn("managerid"), true, false);
this.comp = new TupleComparator(idxSchema, sortKeys);
this.writer =
new BSTIndex(conf)
.getIndexWriter(idxPath, BSTIndex.TWO_LEVEL_INDEX, this.idxSchema, this.comp);
writer.setLoadNum(100);
writer.open();
long offset;
meta = CatalogUtil.newTableMeta(StoreType.CSV);
tablePath = StorageUtil.concatPath(workDir, "employee", "table.csv");
fs = tablePath.getFileSystem(conf);
fs.mkdirs(tablePath.getParent());
FileAppender appender =
(FileAppender)
StorageManagerFactory.getStorageManager(conf).getAppender(meta, schema, tablePath);
appender.init();
Tuple tuple = new VTuple(schema.size());
for (int i = 0; i < 10000; i++) {
Tuple key = new VTuple(this.idxSchema.size());
int rndKey = rnd.nextInt(250);
if (this.randomValues.containsKey(rndKey)) {
int t = this.randomValues.remove(rndKey) + 1;
this.randomValues.put(rndKey, t);
} else {
this.randomValues.put(rndKey, 1);
}
key.put(new Datum[] {DatumFactory.createInt4(rndKey)});
tuple.put(
new Datum[] {
DatumFactory.createInt4(rndKey),
DatumFactory.createInt4(rnd.nextInt(10)),
DatumFactory.createText("dept_" + rnd.nextInt(10))
});
offset = appender.getOffset();
appender.addTuple(tuple);
writer.write(key, offset);
}
appender.flush();
appender.close();
writer.close();
TableDesc desc =
new TableDesc(
CatalogUtil.buildFQName(TajoConstants.DEFAULT_DATABASE_NAME, "employee"),
schema,
meta,
sm.getTablePath("employee"));
catalog.createTable(desc);
analyzer = new SQLAnalyzer();
planner = new LogicalPlanner(catalog);
optimizer = new LogicalOptimizer(conf);
}
@Test
public void testBetween() throws IOException {
Schema schema2 = new Schema();
schema2.addColumn("col1", TEXT);
schema2.addColumn("col2", TEXT);
schema2.addColumn("col3", TEXT);
// constant checker
testEval(
schema2,
"table1",
"b,a,c",
"select col1 between 'a' and 'c' from table1",
new String[] {"t"});
testEval(
schema2,
"table1",
"b,a,c",
"select col1 between 'c' and 'a' from table1",
new String[] {"f"});
testEval(
schema2,
"table1",
"b,a,c",
"select col1 between symmetric 'c' and 'a' from table1",
new String[] {"t"});
testEval(
schema2,
"table1",
"d,a,c",
"select col1 between 'a' and 'c' from table1",
new String[] {"f"});
// tests for inclusive
testEval(
schema2,
"table1",
"a,a,c",
"select col1 between col2 and col3 from table1",
new String[] {"t"});
testEval(
schema2,
"table1",
"b,a,c",
"select col1 between col2 and col3 from table1",
new String[] {"t"});
testEval(
schema2,
"table1",
"c,a,c",
"select col1 between col2 and col3 from table1",
new String[] {"t"});
testEval(
schema2,
"table1",
"d,a,c",
"select col1 between col2 and col3 from table1",
new String[] {"f"});
// tests for asymmetric and symmetric
testEval(
schema2,
"table1",
"b,a,c",
"select col1 between col3 and col2 from table1",
new String[] {"f"});
testEval(
schema2,
"table1",
"b,a,c",
"select col1 between symmetric col3 and col2 from table1",
new String[] {"t"});
}
@Test
public void testColumnKeyValueMapping() throws Exception {
KeyValueSet keyValueSet = new KeyValueSet();
keyValueSet.set(HBaseStorageConstants.META_TABLE_KEY, "test");
keyValueSet.set(HBaseStorageConstants.META_COLUMNS_KEY, ":key,col2:key:,col2:value:#b,col3:");
Schema schema = new Schema();
schema.addColumn("c1", Type.TEXT);
schema.addColumn("c2", Type.TEXT);
schema.addColumn("c3", Type.TEXT);
schema.addColumn("c4", Type.TEXT);
TableMeta tableMeta = new TableMeta("HBASE", keyValueSet);
ColumnMapping columnMapping = new ColumnMapping(schema, tableMeta.getPropertySet());
List<String> cfNames = columnMapping.getColumnFamilyNames();
assertEquals(2, cfNames.size());
assertEquals("col2", cfNames.get(0));
assertEquals("col3", cfNames.get(1));
for (int i = 0; i < columnMapping.getIsBinaryColumns().length; i++) {
if (i == 2) {
assertTrue(columnMapping.getIsBinaryColumns()[i]);
} else {
assertFalse(columnMapping.getIsBinaryColumns()[i]);
}
}
for (int i = 0; i < columnMapping.getIsRowKeyMappings().length; i++) {
if (i == 0) {
assertTrue(columnMapping.getIsRowKeyMappings()[i]);
} else {
assertFalse(columnMapping.getIsRowKeyMappings()[i]);
}
}
String[] expectedColumnNames = {null, null, null, null};
for (int i = 0; i < schema.size(); i++) {
String columnName =
columnMapping.getMappingColumns()[i][1] == null
? null
: new String(columnMapping.getMappingColumns()[i][1]);
assertEquals(expectedColumnNames[i], columnName);
}
for (int i = 0; i < schema.size(); i++) {
if (i == 1) {
assertTrue(columnMapping.getIsColumnKeys()[i]);
} else {
assertFalse(columnMapping.getIsColumnKeys()[i]);
}
}
for (int i = 0; i < schema.size(); i++) {
if (i == 2) {
assertTrue(columnMapping.getIsColumnValues()[i]);
} else {
assertFalse(columnMapping.getIsColumnValues()[i]);
}
}
}
