/**
* Copyright 2010 Nube Technologies
*
* Licensed 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.
*/
package co.nubetech.hiho.hive;
import static org.junit.Assert.assertEquals;
import java.sql.Types;
import java.util.ArrayList;
import org.apache.hadoop.conf.Configuration;
import org.junit.Test;
import co.nubetech.hiho.mapreduce.lib.db.apache.DBConfiguration;
import co.nubetech.hiho.common.HIHOConf;
import co.nubetech.hiho.common.HIHOException;
import co.nubetech.hiho.job.DBQueryInputJob;
import co.nubetech.hiho.mapreduce.lib.db.ColumnInfo;
import co.nubetech.hiho.mapreduce.lib.db.GenericDBWritable;
public class TestHiveUtility {
@Test
public void testGetSelectQuery() throws HIHOException {
Configuration conf = new Configuration();
Configuration conf1 = new Configuration();
conf.set(DBConfiguration.INPUT_FIELD_NAMES_PROPERTY, "id,name");
conf.set(DBConfiguration.INPUT_TABLE_NAME_PROPERTY, "student");
// conf.set(DBConfiguration.INPUT_CONDITIONS_PROPERTY,"");
// conf.set(DBConfiguration.INPUT_QUERY,"select * from student");
conf1.set(DBConfiguration.INPUT_FIELD_NAMES_PROPERTY,
"empId,empName,empAddress");
conf1.set(DBConfiguration.INPUT_TABLE_NAME_PROPERTY, "employee");
// conf1.set(DBConfiguration.INPUT_CONDITIONS_PROPERTY,"");
conf1.set(DBConfiguration.INPUT_QUERY, "select * from employee");
String dbProductName = "MYSQL";
assertEquals("SELECT id, name FROM student AS student",
DBQueryInputJob.getSelectQuery(conf, dbProductName));
assertEquals("select * from employee",
DBQueryInputJob.getSelectQuery(conf1, dbProductName));
}
@Test
public void testGetTableName() throws HIHOException {
Configuration conf = new Configuration();
Configuration conf1 = new Configuration();
Configuration conf2 = new Configuration();
conf.set(DBConfiguration.INPUT_TABLE_NAME_PROPERTY, "student");
conf.set(DBConfiguration.INPUT_QUERY, "select * from student");
// conf.set(HIHOConf.HIVE_MULTIPLE_PARTITION_BY,
// "country:string:us,ca");
conf.set(HIHOConf.HIVE_TABLE_NAME, "hive");
conf1.set(DBConfiguration.INPUT_QUERY, "select * from student");
conf2.set(DBConfiguration.INPUT_TABLE_NAME_PROPERTY, "employee");
assertEquals("hive", HiveUtility.getTableName(conf));
assertEquals("student", HiveUtility.getTableName(conf1));
assertEquals("employee", HiveUtility.getTableName(conf2));
}
@Test
public void testGetInsertQuery() throws HIHOException {
ColumnInfo intColumn = new ColumnInfo(0, Types.INTEGER, "intColumn");
ColumnInfo stringColumn = new ColumnInfo(1, Types.VARCHAR,
"stringColumn");
ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
columns.add(intColumn);
columns.add(stringColumn);
// HiveUtility.tableName = "employee";
GenericDBWritable writable = new GenericDBWritable(columns, null);
Configuration conf = new Configuration();
conf.set(HIHOConf.HIVE_PARTITION_BY, "country:string:us,name:string");
conf.set(DBConfiguration.INPUT_TABLE_NAME_PROPERTY, "employee");
assertEquals(
"FROM `employeetmp` tmp INSERT OVERWRITE TABLE `employee` PARTITION ( country='us',name) SELECT `tmp`.`intColumn`,`tmp`.`stringColumn`",
HiveUtility.getInsertQueryFromTmpToMain(conf, writable,
conf.get(HIHOConf.HIVE_PARTITION_BY)));
}
@Test
public void testGetTableColumns() throws HIHOException {
ColumnInfo intColumn = new ColumnInfo(0, Types.INTEGER, "intColumn");
ColumnInfo stringColumn = new ColumnInfo(1, Types.VARCHAR,
"stringColumn");
ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
columns.add(intColumn);
columns.add(stringColumn);
GenericDBWritable writable = new GenericDBWritable(columns, null);
assertEquals("`tmp`.`intColumn`,`tmp`.`stringColumn`",
HiveUtility.getTmpTableColumns(writable));
}
@Test
public void testGetTmpCreateQuery() throws HIHOException {
ColumnInfo intColumn = new ColumnInfo(0, Types.INTEGER, "intColumn");
ColumnInfo stringColumn = new ColumnInfo(1, Types.VARCHAR,
"stringColumn");
ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
columns.add(intColumn);
columns.add(stringColumn);
Configuration conf = new Configuration();
conf.set(DBConfiguration.INPUT_TABLE_NAME_PROPERTY, "employee");
GenericDBWritable writable = new GenericDBWritable(columns, null);
conf.set(HIHOConf.INPUT_OUTPUT_STRATEGY, "DELIMITED");
conf.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
assertEquals(
"CREATE TABLE `employeetmp` ( `intColumn` int,`stringColumn` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE",
HiveUtility.getTmpCreateQuery(conf, writable));
}
@Test
public void testGetPartitionBy() {
String partitionBy = "country:string:us;name:string:jack";
assertEquals("country string,name string",
HiveUtility.getPartitionBy(partitionBy));
}
@Test
public void testGetColumnsForPartitionedCreateTables() {
Configuration conf = new Configuration();
conf.set(HIHOConf.HIVE_PARTITION_BY, "country:string:us;name:string:");
String columns = "id int,name string";
assertEquals(" id int",
HiveUtility.getColumnsForPartitionedCreateTables(conf, columns));
}
// @Test(expected = HIHOException.class)
@Test(expected = HIHOException.class)
public void testGetCreateQuery() throws HIHOException {
ColumnInfo intColumn = new ColumnInfo(0, Types.INTEGER, "id");
ColumnInfo stringColumn = new ColumnInfo(1, Types.VARCHAR, "country");
ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
columns.add(intColumn);
columns.add(stringColumn); // HiveUtility.// = "employee";
GenericDBWritable writable = new GenericDBWritable(columns, null);
// This is normal case to generate basic create query
Configuration conf = new Configuration();
conf.set(HIHOConf.HIVE_TABLE_NAME, "employee");
conf.set(HIHOConf.INPUT_OUTPUT_STRATEGY, "DELIMITED");
conf.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ";");
assertEquals(
"CREATE TABLE `employee` ( `id` int,`country` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ';' STORED AS TEXTFILE",
HiveUtility.getCreateQuery(conf, writable));
// This case show create query when clusteredBy and sortedBy
// configuration is given user,please make a note sortedBy feature will
// not work till clusteredBy feature is not defined.
Configuration conf1 = new Configuration();
conf1.set(HIHOConf.HIVE_TABLE_NAME, "employee");
conf1.set(HIHOConf.INPUT_OUTPUT_STRATEGY, "DELIMITED");
conf1.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
conf1.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
conf1.set(HIHOConf.HIVE_CLUSTERED_BY, "name:2");
conf1.set(HIHOConf.HIVE_SORTED_BY, "name");
// System.out.println(HiveUtility.getCreateQuery(conf1, writable));
assertEquals(
"CREATE TABLE `employee` ( `id` int,`country` string) CLUSTERED BY (name) SORTED BY (name) INTO 2 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE",
HiveUtility.getCreateQuery(conf1, writable));
// This case is when user has defined partitionedBy configuration
Configuration conf3 = new Configuration();
conf3.set(HIHOConf.HIVE_TABLE_NAME, "employee");
conf3.set(HIHOConf.INPUT_OUTPUT_STRATEGY, "DELIMITED");
conf3.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
conf3.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
conf3.set(HIHOConf.HIVE_CLUSTERED_BY, "name:2");
conf3.set(HIHOConf.HIVE_SORTED_BY, "name");
conf3.set(HIHOConf.HIVE_PARTITION_BY, "country:string");
String partitionBy = "country:string";
// String partitionBy1 = "name:string:raj,country:string";
// System.out.println(HiveUtility.getCreateQuery(conf3,
// writable,partitionBy));
assertEquals(
"CREATE TABLE `employee` ( `id` int) PARTITIONED BY (country string) CLUSTERED BY (name) SORTED BY (name) INTO 2 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE",
HiveUtility.getCreateQuery(conf3, writable, partitionBy));
// This is case of dynamicPartition when static and dynamic both
// partitions are defined
Configuration conf4 = new Configuration();
conf4.set(HIHOConf.HIVE_TABLE_NAME, "employee");
conf4.set(HIHOConf.INPUT_OUTPUT_STRATEGY, "DELIMITED");
conf4.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
conf4.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
conf4.set(HIHOConf.HIVE_CLUSTERED_BY, "name:2");
conf4.set(HIHOConf.HIVE_SORTED_BY, "name");
conf4.set(HIHOConf.HIVE_PARTITION_BY, "name:string:raj;country:string");
// String partitionBy = "country:string";
String partitionBy1 = "name:string:raj;country:string";
System.out.println(HiveUtility.getCreateQuery(conf4, writable,
partitionBy1));
assertEquals(
"CREATE TABLE `employee` ( `id` int) PARTITIONED BY (name string,country string) CLUSTERED BY (name) SORTED BY (name) INTO 2 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE",
HiveUtility.getCreateQuery(conf4, writable, partitionBy1));
// This case will throw HIHOException as with clusteredBy conf number of
// buckets is not defined,it must be defined as name:2
// please make a note that this case is true for both getCreateQuery()
// function
Configuration conf2 = new Configuration();
conf2.set(HIHOConf.INPUT_OUTPUT_STRATEGY, "DELIMITED");
conf2.set(HIHOConf.INPUT_OUTPUT_DELIMITER, ",");
conf2.set(HIHOConf.HIVE_CLUSTERED_BY, "name");
conf2.set(HIHOConf.HIVE_TABLE_NAME, "employee");
HiveUtility.getCreateQuery(conf2, writable);
}
@Test
public void testGetcolumns() throws HIHOException {
ColumnInfo intColumn = new ColumnInfo(0, Types.INTEGER, "id");
ColumnInfo stringColumn = new ColumnInfo(1, Types.VARCHAR, "country");
ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
columns.add(intColumn);
columns.add(stringColumn); // HiveUtility.// = "employee";
GenericDBWritable writable = new GenericDBWritable(columns, null);
assertEquals("`id` int,`country` string",
HiveUtility.getColumns(writable));
}
@Test
public void testGetDynamicPartitionBy() {
String partitionBy = "name:string:raj;country:string";
String partitionBy1 = "name:string";
assertEquals("country", HiveUtility.getDynamicPartitionBy(partitionBy));
assertEquals("name", HiveUtility.getDynamicPartitionBy(partitionBy1));
}
@Test
public void testGetLoadQuery() throws HIHOException {
ColumnInfo intColumn = new ColumnInfo(0, Types.INTEGER, "intColumn");
ColumnInfo stringColumn = new ColumnInfo(1, Types.VARCHAR,
"stringColumn");
ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
columns.add(intColumn);
columns.add(stringColumn);
// HiveUtility.tableName = "employee";
GenericDBWritable writable = new GenericDBWritable(columns, null);
Configuration config = new Configuration();
// String partitionBy = "country:string";
String partitionBy1 = "country:string:us";
config.set(HIHOConf.INPUT_OUTPUT_PATH, "/user/nube/tableForHiho");
config.set(HIHOConf.HIVE_TABLE_NAME, "employee");
config.set(HIHOConf.HIVE_PARTITION_BY, "country:string:us");
assertEquals(
"LOAD DATA INPATH '/user/nube/tableForHiho' OVERWRITE INTO TABLE `employee` PARTITION ( country='us')",
HiveUtility.getLoadQuery(config,
config.get(HIHOConf.INPUT_OUTPUT_PATH), writable,
partitionBy1));
Configuration config1 = new Configuration();
String partitionBy = "country:string";
// String partitionBy1 = "country:string:us";
config1.set(HIHOConf.INPUT_OUTPUT_PATH, "/user/nube/tableForHiho");
config1.set(HIHOConf.HIVE_TABLE_NAME, "employee");
// config1.set(HIHOConf.HIVE_PARTITION_BY, "country:string:us");
assertEquals(
"LOAD DATA INPATH '/user/nube/tableForHiho' OVERWRITE INTO TABLE `employee`",
HiveUtility.getLoadQuery(config1,
config.get(HIHOConf.INPUT_OUTPUT_PATH), writable));
}
public void testGetColumnType() throws HIHOException {
assertEquals("int", HiveUtility.getColumnType(Types.INTEGER));
assertEquals("long", HiveUtility.getColumnType(Types.BIGINT));
assertEquals("float", HiveUtility.getColumnType(Types.FLOAT));
assertEquals("double", HiveUtility.getColumnType(Types.DOUBLE));
assertEquals("string", HiveUtility.getColumnType(Types.CHAR));
assertEquals("bytearray", HiveUtility.getColumnType(Types.BINARY));
assertEquals("bytearray", HiveUtility.getColumnType(Types.BLOB));
assertEquals("bytearray", HiveUtility.getColumnType(Types.CLOB));
}
}