Author: rareddy Date: 2011-11-13 14:08:32 -0500 (Sun, 13 Nov 2011) New Revision: 3643 Added: trunk/build/kits/jboss-container/teiid-examples/jca/hive-ds.xml trunk/connectors/translator-hive/ trunk/connectors/translator-hive/.settings/ trunk/connectors/translator-hive/pom.xml trunk/connectors/translator-hive/src/ trunk/connectors/translator-hive/src/main/ trunk/connectors/translator-hive/src/main/java/ trunk/connectors/translator-hive/src/main/java/org/ trunk/connectors/translator-hive/src/main/java/org/teiid/ trunk/connectors/translator-hive/src/main/java/org/teiid/translator/ trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/ trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveExecutionFactory.java trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveMetadataProcessor.java trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveSQLConversionVisitor.java trunk/connectors/translator-hive/src/main/resources/ trunk/connectors/translator-hive/src/main/resources/META-INF/ trunk/connectors/translator-hive/src/test/ trunk/connectors/translator-hive/src/test/java/ trunk/connectors/translator-hive/src/test/java/org/ trunk/connectors/translator-hive/src/test/java/org/teiid/ trunk/connectors/translator-hive/src/test/java/org/teiid/translator/ trunk/connectors/translator-hive/src/test/java/org/teiid/translator/hive/ trunk/connectors/translator-hive/src/test/java/org/teiid/translator/hive/TestHiveExecutionFactory.java Modified: trunk/build/assembly/jboss-container/dist.xml trunk/connectors/pom.xml trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ConvertModifier.java trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml Log: TEIID-1810: Adding Hive translator to Teiid Modified: trunk/build/assembly/jboss-container/dist.xml =================================================================== --- trunk/build/assembly/jboss-container/dist.xml 2011-11-13 18:31:49 UTC (rev 3642) +++ trunk/build/assembly/jboss-container/dist.xml 2011-11-13 19:08:32 UTC (rev 3643) @@ -185,6 +185,7 @@ <include>org.jboss.teiid.connectors:translator-salesforce</include> <include>org.jboss.teiid.connectors:translator-ws</include> <include>org.jboss.teiid.connectors:translator-olap</include> + <include>org.jboss.teiid.connectors:translator-hive</include> </includes> <binaries> Added: trunk/build/kits/jboss-container/teiid-examples/jca/hive-ds.xml =================================================================== --- trunk/build/kits/jboss-container/teiid-examples/jca/hive-ds.xml (rev 0) +++ trunk/build/kits/jboss-container/teiid-examples/jca/hive-ds.xml 2011-11-13 19:08:32 UTC (rev 3643) @@ -0,0 +1,20 @@ +<?xml version="1.0" encoding="UTF-8"?> +<datasources> + <!-- + Copy the following jars into the "lib" directory for Hive JDBC driver. These jar files can be + found Hive/lib and Hadoop/lib directories. + + hadoop-core-0.20.203.0.jar + hive-exec-0.7.1.jar + hive-jdbc-0.7.1.jar + hive-metastore-0.7.1.jar + hive-service-0.7.1.jar + libfb303.jar + --> + <local-tx-datasource> + <jndi-name>HiveDS</jndi-name> + <connection-url>jdbc:hive://localhost:10000/default</connection-url> + <driver-class>org.apache.hadoop.hive.jdbc.HiveDriver</driver-class> + </local-tx-datasource> + +</datasources> Property changes on: trunk/build/kits/jboss-container/teiid-examples/jca/hive-ds.xml ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/connectors/pom.xml =================================================================== --- trunk/connectors/pom.xml 2011-11-13 18:31:49 UTC (rev 3642) +++ trunk/connectors/pom.xml 2011-11-13 19:08:32 UTC (rev 3643) @@ -87,5 +87,6 @@ <module>sandbox</module> <module>translator-ws</module> <module>translator-olap</module> + <module>translator-hive</module> </modules> </project> Added: trunk/connectors/translator-hive/pom.xml =================================================================== --- trunk/connectors/translator-hive/pom.xml (rev 0) +++ trunk/connectors/translator-hive/pom.xml 2011-11-13 19:08:32 UTC (rev 3643) @@ -0,0 +1,66 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> + <parent> + <artifactId>connectors</artifactId> + <groupId>org.jboss.teiid</groupId> + <version>teiid-7.6.0.CR1-SNAPSHOT</version> + </parent> + <modelVersion>4.0.0</modelVersion> + <artifactId>translator-hive</artifactId> + <groupId>org.jboss.teiid.connectors</groupId> + <name>Hive Translator</name> + <description>This translator provides access HDFS system using Hive.</description> + + <dependencies> + <dependency> + <groupId>org.jboss.teiid</groupId> + <artifactId>teiid-api</artifactId> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.jboss.teiid</groupId> + <artifactId>teiid-common-core</artifactId> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.jboss.teiid.connectors</groupId> + <artifactId>translator-jdbc</artifactId> + <version>${version}</version> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.jboss.teiid.connectors</groupId> + <artifactId>translator-jdbc</artifactId> + <version>${version}</version> + <type>test-jar</type> + <scope>test</scope> + </dependency> + <dependency> + <groupId>javax.resource</groupId> + <artifactId>connector-api</artifactId> + <scope>provided</scope> + </dependency> + </dependencies> + + <build> + <outputDirectory>target/classes</outputDirectory> + <resources> + <resource> + <directory>src/main/resources</directory> + <filtering>true</filtering> + <includes> + <include>**/*.xml</include> + <include>**/*.properties</include> + </includes> + </resource> + <resource> + <directory>src/main/resources</directory> + <filtering>false</filtering> + <excludes> + <exclude>**/*.xml</exclude> + <exclude>**/*.properties</exclude> + </excludes> + </resource> + </resources> + </build> +</project> Property changes on: trunk/connectors/translator-hive/pom.xml ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveExecutionFactory.java =================================================================== --- trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveExecutionFactory.java (rev 0) +++ trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveExecutionFactory.java 2011-11-13 19:08:32 UTC (rev 3643) @@ -0,0 +1,350 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ +package org.teiid.translator.hive; + +import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.BIG_INTEGER; +import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.DATE; +import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.DOUBLE; +import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.INTEGER; +import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.OBJECT; +import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.STRING; + +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.Time; +import java.sql.Timestamp; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +import org.teiid.core.util.PropertiesUtils; +import org.teiid.language.Command; +import org.teiid.language.Function; +import org.teiid.language.Limit; +import org.teiid.metadata.MetadataFactory; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.SourceSystemFunctions; +import org.teiid.translator.Translator; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.AliasModifier; +import org.teiid.translator.jdbc.ConvertModifier; +import org.teiid.translator.jdbc.FunctionModifier; +import org.teiid.translator.jdbc.JDBCExecutionFactory; +import org.teiid.translator.jdbc.ModFunctionModifier; +import org.teiid.translator.jdbc.SQLConversionVisitor; + +@Translator(name="hive", description="A translator for hive based database on HDFS") +public class HiveExecutionFactory extends JDBCExecutionFactory { + + public static String HIVE = "hive"; //$NON-NLS-1$ + protected ConvertModifier convert = new ConvertModifier(); + + + public HiveExecutionFactory() { + setSupportedJoinCriteria(SupportedJoinCriteria.EQUI); + } + + @Override + public void start() throws TranslatorException { + super.start(); + convert.addTypeMapping("tinyint", FunctionModifier.BYTE); //$NON-NLS-1$ + convert.addTypeMapping("smallint", FunctionModifier.SHORT); //$NON-NLS-1$ + convert.addTypeMapping("int", FunctionModifier.INTEGER); //$NON-NLS-1$ + convert.addTypeMapping("bigint", FunctionModifier.BIGINTEGER, FunctionModifier.LONG); //$NON-NLS-1$ + convert.addTypeMapping("boolean", FunctionModifier.BOOLEAN); //$NON-NLS-1$ + convert.addTypeMapping("double", FunctionModifier.DOUBLE, FunctionModifier.BIGDECIMAL); //$NON-NLS-1$ + convert.addTypeMapping("float", FunctionModifier.FLOAT); //$NON-NLS-1$ + convert.addTypeMapping("string", FunctionModifier.STRING); //$NON-NLS-1$ + + // unsupported types + //FunctionModifier.DATE, + //FunctionModifier.TIMESTAMP + //FunctionModifier.TIME, + //FunctionModifier.CHAR, + //FunctionModifier.BLOB, + //FunctionModifier.CLOB, + //FunctionModifier.XML + + registerFunctionModifier(SourceSystemFunctions.CONVERT, convert); + + registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("&")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("~")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("&")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.BITXOR, new AliasModifier("|")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("unix_timestamp")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("%", getLanguageFactory(), Arrays.asList(TypeFacility.RUNTIME_TYPES.BIG_INTEGER, TypeFacility.RUNTIME_TYPES.BIG_DECIMAL))); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.ARRAY_GET, new FunctionModifier() { + + @Override + public List<?> translate(Function function) { + return Arrays.asList(function.getParameters().get(0), '[', function.getParameters().get(1), ']'); + } + }); + + + addPushDownFunction(HIVE, "lower", STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(HIVE, "upper", STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(HIVE, "positive", INTEGER, DOUBLE); //$NON-NLS-1$ + addPushDownFunction(HIVE, "positive", DOUBLE, DOUBLE); //$NON-NLS-1$ + addPushDownFunction(HIVE, "negitive", INTEGER, DOUBLE); //$NON-NLS-1$ + addPushDownFunction(HIVE, "negitive", DOUBLE, DOUBLE); //$NON-NLS-1$ + addPushDownFunction(HIVE, "ln", DOUBLE, DOUBLE); //$NON-NLS-1$ + addPushDownFunction(HIVE, "reverse", STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(HIVE, "space", INTEGER, STRING); //$NON-NLS-1$ + addPushDownFunction(HIVE, "split", OBJECT, STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(HIVE, "hex", STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(HIVE, "unhex", STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(HIVE, "bin", STRING, BIG_INTEGER); //$NON-NLS-1$ + addPushDownFunction(HIVE, "day", INTEGER, DATE); //$NON-NLS-1$ + addPushDownFunction(HIVE, "datediff", INTEGER, DATE, DATE); //$NON-NLS-1$ + addPushDownFunction(HIVE, "date_add", INTEGER, DATE, INTEGER); //$NON-NLS-1$ + addPushDownFunction(HIVE, "date_sub", INTEGER, DATE, INTEGER); //$NON-NLS-1$ + } + + @Override + public SQLConversionVisitor getSQLConversionVisitor() { + return new HiveSQLConversionVisitor(this); + } + + @Override + public boolean useAnsiJoin() { + return true; + } + + @Override + public boolean supportsBetweenCriteria() { + return false; + } + + @Override + public boolean supportsCaseExpressions() { + //https://issues.apache.org/jira/browse/HIVE-164 + return true; + } + + @Override + public boolean supportsCorrelatedSubqueries() { + //https://issues.apache.org/jira/browse/HIVE-784 + return false; + } + + @Override + public boolean supportsExistsCriteria() { + return false; + } + + @Override + public boolean supportsInCriteriaSubquery() { + // the website documents a way to semi-join to re-write this but did not handle NOT IN case. + return false; + } + + @Override + public boolean supportsLikeCriteriaEscapeCharacter() { + return false; + } + + @Override + public boolean supportsQuantifiedCompareCriteriaAll() { + return false; + } + + @Override + public boolean supportsQuantifiedCompareCriteriaSome() { + return false; + } + + @Override + public boolean supportsBulkUpdate() { + return false; + } + + @Override + public boolean supportsBatchedUpdates() { + return false; + } + + @Override + public List<?> translateCommand(Command command, ExecutionContext context) { + return null; + } + + @Override + public List<?> translateLimit(Limit limit, ExecutionContext context) { + return null; + } + + @Override + public boolean addSourceComment() { + return false; + } + + @Override + public boolean useAsInGroupAlias(){ + return false; + } + + public boolean hasTimeType() { + return false; + } + + public String getLikeRegexString() { + return "REGEXP"; //$NON-NLS-1$ + } + + @Override + public boolean supportsScalarSubqueries() { + // Supported only in FROM clause + return false; + } + + @Override + public boolean supportsInlineViews() { + // must be aliased. + return true; + } + + @Override + public boolean supportsUnions() { + return true; + // only union all in subquery + } + + @Override + public boolean supportsInsertWithQueryExpression() { + return false; // insert seems to be only with overwrite always + } + + @Override + public boolean supportsIntersect() { + return false; + } + + @Override + public boolean supportsExcept() { + return false; + } + + @Override + public boolean supportsAggregatesEnhancedNumeric() { + return true; + } + + @Override + public boolean supportsCommonTableExpressions() { + return false; + } + + @Override + public String translateLiteralBoolean(Boolean booleanValue) { + if(booleanValue.booleanValue()) { + return "true"; //$NON-NLS-1$ + } + return "false"; //$NON-NLS-1$ + } + + @Override + public String translateLiteralDate(java.sql.Date dateValue) { + return formatDateValue(dateValue); + } + + @Override + public String translateLiteralTime(Time timeValue) { + if (!hasTimeType()) { + return translateLiteralTimestamp(new Timestamp(timeValue.getTime())); + } + return formatDateValue(timeValue); + } + + @Override + public String translateLiteralTimestamp(Timestamp timestampValue) { + return formatDateValue(timestampValue); + } + + @Override + public List<String> getSupportedFunctions() { + List<String> supportedFunctions = new ArrayList<String>(); + supportedFunctions.addAll(super.getSupportedFunctions()); + + supportedFunctions.add(SourceSystemFunctions.ABS); + supportedFunctions.add(SourceSystemFunctions.ACOS); + supportedFunctions.add(SourceSystemFunctions.ARRAY_GET); + supportedFunctions.add(SourceSystemFunctions.ASIN); + supportedFunctions.add(SourceSystemFunctions.ASCII); + supportedFunctions.add(SourceSystemFunctions.ATAN); + supportedFunctions.add(SourceSystemFunctions.BITAND); + supportedFunctions.add(SourceSystemFunctions.BITNOT); + supportedFunctions.add(SourceSystemFunctions.BITOR); + supportedFunctions.add(SourceSystemFunctions.BITXOR); + supportedFunctions.add(SourceSystemFunctions.CEILING); + supportedFunctions.add(SourceSystemFunctions.COALESCE); + supportedFunctions.add(SourceSystemFunctions.CONCAT); + supportedFunctions.add(SourceSystemFunctions.COS); + supportedFunctions.add(SourceSystemFunctions.CONVERT); + supportedFunctions.add(SourceSystemFunctions.CURDATE); + supportedFunctions.add(SourceSystemFunctions.CURTIME); + supportedFunctions.add(SourceSystemFunctions.DEGREES); + supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH); + supportedFunctions.add(SourceSystemFunctions.EXP); + supportedFunctions.add(SourceSystemFunctions.FLOOR); + supportedFunctions.add(SourceSystemFunctions.HOUR); + supportedFunctions.add(SourceSystemFunctions.IFNULL); + supportedFunctions.add(SourceSystemFunctions.LCASE); + supportedFunctions.add(SourceSystemFunctions.LOCATE); + supportedFunctions.add(SourceSystemFunctions.LPAD); + supportedFunctions.add(SourceSystemFunctions.LENGTH); + supportedFunctions.add(SourceSystemFunctions.LTRIM); + supportedFunctions.add(SourceSystemFunctions.LOG); + supportedFunctions.add(SourceSystemFunctions.LOG10); + supportedFunctions.add(SourceSystemFunctions.MINUTE); + supportedFunctions.add(SourceSystemFunctions.MOD); + supportedFunctions.add(SourceSystemFunctions.POWER); + supportedFunctions.add(SourceSystemFunctions.SECOND); + supportedFunctions.add(SourceSystemFunctions.SQRT); + supportedFunctions.add(SourceSystemFunctions.RAND); + supportedFunctions.add(SourceSystemFunctions.ROUND); + supportedFunctions.add(SourceSystemFunctions.RTRIM); + supportedFunctions.add(SourceSystemFunctions.RPAD); + supportedFunctions.add(SourceSystemFunctions.MONTH); + supportedFunctions.add(SourceSystemFunctions.PI); + supportedFunctions.add(SourceSystemFunctions.SIN); + supportedFunctions.add(SourceSystemFunctions.SUBSTRING); + supportedFunctions.add(SourceSystemFunctions.TRIM); + supportedFunctions.add(SourceSystemFunctions.UCASE); + supportedFunctions.add(SourceSystemFunctions.YEAR); + return supportedFunctions; + } + + @Override + public void getMetadata(MetadataFactory metadataFactory, Connection conn) throws TranslatorException { + try { + HiveMetadataProcessor metadataProcessor = new HiveMetadataProcessor(); + PropertiesUtils.setBeanProperties(metadataProcessor, metadataFactory.getImportProperties(), "importer"); //$NON-NLS-1$ + metadataProcessor.getConnectorMetadata(conn, metadataFactory); + } catch (SQLException e) { + throw new TranslatorException(e); + } + } + +} Property changes on: trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveExecutionFactory.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveMetadataProcessor.java =================================================================== --- trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveMetadataProcessor.java (rev 0) +++ trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveMetadataProcessor.java 2011-11-13 19:08:32 UTC (rev 3643) @@ -0,0 +1,113 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ +package org.teiid.translator.hive; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.List; + +import org.teiid.logging.LogConstants; +import org.teiid.logging.LogManager; +import org.teiid.metadata.Column; +import org.teiid.metadata.MetadataFactory; +import org.teiid.metadata.Table; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.JDBCMetdataProcessor; + +public class HiveMetadataProcessor extends JDBCMetdataProcessor { + + @Override + public void getConnectorMetadata(Connection conn, MetadataFactory metadataFactory) throws SQLException, TranslatorException { + List<String> tables = getTables(conn); + for (String table:tables) { + addTable(table, conn, metadataFactory); + } + } + + private List<String> getTables(Connection conn) { + ArrayList<String> tables = new ArrayList<String>(); + try { + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery("SHOW TABLES"); //$NON-NLS-1$ + if (rs.next()){ + tables.add(rs.getString(1)); + } + rs.close(); + } catch (SQLException e) { + LogManager.logDetail(LogConstants.CTX_CONNECTOR, "HiveMetadataProcessor - failed getting table names"); //$NON-NLS-1$ + } + return tables; + } + + private String getRuntimeType(String type) { + if (type.equalsIgnoreCase("int")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.INTEGER; + } + else if (type.equalsIgnoreCase("tinyint")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.BYTE; + } + else if (type.equalsIgnoreCase("smallint")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.SHORT; + } + else if (type.equalsIgnoreCase("bigint")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.BIG_INTEGER; + } + else if (type.equalsIgnoreCase("string")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.STRING; + } + else if (type.equalsIgnoreCase("float")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.FLOAT; + } + else if (type.equalsIgnoreCase("double")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.DOUBLE; + } + else if (type.equalsIgnoreCase("boolean")) { //$NON-NLS-1$ + return TypeFacility.RUNTIME_NAMES.BOOLEAN; + } + return TypeFacility.RUNTIME_NAMES.STRING; + } + private void addTable(String tableName, Connection conn, MetadataFactory metadataFactory) throws TranslatorException { + try { + Table table = metadataFactory.addTable(tableName); + table.setNameInSource(tableName); + table.setSupportsUpdate(true); + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery("DESCRIBE "+tableName); //$NON-NLS-1$ + if (rs.next()){ + String name = rs.getString(1); + String type = rs.getString(2); + String runtimeType = getRuntimeType(type); + + Column column = metadataFactory.addColumn(name, runtimeType, table); + column.setNameInSource(name); + column.setUpdatable(true); + } + rs.close(); + } catch (SQLException e) { + LogManager.logDetail(LogConstants.CTX_CONNECTOR, "HiveMetadataProcessor - failed getting column names"); //$NON-NLS-1$ + } + } +} Property changes on: trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveMetadataProcessor.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveSQLConversionVisitor.java =================================================================== --- trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveSQLConversionVisitor.java (rev 0) +++ trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveSQLConversionVisitor.java 2011-11-13 19:08:32 UTC (rev 3643) @@ -0,0 +1,185 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ +package org.teiid.translator.hive; + +import static org.teiid.language.SQLConstants.Reserved.ALL; +import static org.teiid.language.SQLConstants.Reserved.DISTINCT; +import static org.teiid.language.SQLConstants.Reserved.FROM; +import static org.teiid.language.SQLConstants.Reserved.FULL; +import static org.teiid.language.SQLConstants.Reserved.JOIN; +import static org.teiid.language.SQLConstants.Reserved.LEFT; +import static org.teiid.language.SQLConstants.Reserved.ON; +import static org.teiid.language.SQLConstants.Reserved.OUTER; +import static org.teiid.language.SQLConstants.Reserved.RIGHT; +import static org.teiid.language.SQLConstants.Reserved.SELECT; + +import java.util.List; + +import org.teiid.language.ColumnReference; +import org.teiid.language.Condition; +import org.teiid.language.DerivedColumn; +import org.teiid.language.Expression; +import org.teiid.language.Join; +import org.teiid.language.Limit; +import org.teiid.language.OrderBy; +import org.teiid.language.SQLConstants.Tokens; +import org.teiid.language.Select; +import org.teiid.language.SetQuery; +import org.teiid.language.TableReference; +import org.teiid.translator.jdbc.SQLConversionVisitor; + +public class HiveSQLConversionVisitor extends SQLConversionVisitor { + + public HiveSQLConversionVisitor(HiveExecutionFactory hef) { + super(hef); + } + + public void visit(Join obj) { + TableReference leftItem = obj.getLeftItem(); + if(useParensForJoins() && leftItem instanceof Join) { + buffer.append(Tokens.LPAREN); + append(leftItem); + buffer.append(Tokens.RPAREN); + } else { + append(leftItem); + } + buffer.append(Tokens.SPACE); + + switch(obj.getJoinType()) { + case CROSS_JOIN: + // Hive just works with "JOIN" keyword no inner or cross + //buffer.append(CROSS); + break; + case FULL_OUTER_JOIN: + buffer.append(FULL) + .append(Tokens.SPACE) + .append(OUTER); + break; + case INNER_JOIN: + // Hive just works with "JOIN" keyword no inner or cross + //buffer.append(INNER); + break; + case LEFT_OUTER_JOIN: + buffer.append(LEFT) + .append(Tokens.SPACE) + .append(OUTER); + break; + case RIGHT_OUTER_JOIN: + buffer.append(RIGHT) + .append(Tokens.SPACE) + .append(OUTER); + break; + default: buffer.append(UNDEFINED); + } + buffer.append(Tokens.SPACE) + .append(JOIN) + .append(Tokens.SPACE); + + TableReference rightItem = obj.getRightItem(); + if(rightItem instanceof Join && (useParensForJoins() || obj.getJoinType() == Join.JoinType.CROSS_JOIN)) { + buffer.append(Tokens.LPAREN); + append(rightItem); + buffer.append(Tokens.RPAREN); + } else { + append(rightItem); + } + + final Condition condition = obj.getCondition(); + if (condition != null) { + buffer.append(Tokens.SPACE) + .append(ON) + .append(Tokens.SPACE); + append(condition); + } + } + + public void addColumns(List<DerivedColumn> items) { + if (items != null && items.size() != 0) { + addColumn(items.get(0)); + for (int i = 1; i < items.size(); i++) { + buffer.append(Tokens.COMMA) + .append(Tokens.SPACE); + addColumn(items.get(i)); + } + } + } + + private void addColumn(DerivedColumn dc) { + if (dc.getAlias() != null) { + buffer.append(dc.getAlias()); + } + else { + Expression expr = dc.getExpression(); + if (expr instanceof ColumnReference) { + buffer.append(((ColumnReference)expr).getName()); + } + else { + append(expr); + } + } + } + + @Override + public void visit(SetQuery obj) { + if (obj.getWith() != null) { + append(obj.getWith()); + } + + Select select = (Select)obj.getLeftQuery(); + buffer.append(SELECT).append(Tokens.SPACE); + if(!obj.isAll()) { + buffer.append(DISTINCT).append(Tokens.SPACE); + } + addColumns(select.getDerivedColumns()); + buffer.append(Tokens.SPACE); + buffer.append(FROM).append(Tokens.SPACE); + buffer.append(Tokens.LPAREN); + + appendSetQuery(obj, obj.getLeftQuery(), false); + + buffer.append(Tokens.SPACE); + + appendSetOperation(obj.getOperation()); + + // UNION "ALL" always + buffer.append(Tokens.SPACE); + buffer.append(ALL); + buffer.append(Tokens.SPACE); + + appendSetQuery(obj, obj.getRightQuery(), true); + + OrderBy orderBy = obj.getOrderBy(); + if(orderBy != null) { + buffer.append(Tokens.SPACE); + append(orderBy); + } + + Limit limit = obj.getLimit(); + if(limit != null) { + buffer.append(Tokens.SPACE); + append(limit); + } + buffer.append(Tokens.RPAREN); + buffer.append(Tokens.SPACE); + buffer.append("X__"); //$NON-NLS-1$ + } +} Property changes on: trunk/connectors/translator-hive/src/main/java/org/teiid/translator/hive/HiveSQLConversionVisitor.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: trunk/connectors/translator-hive/src/test/java/org/teiid/translator/hive/TestHiveExecutionFactory.java =================================================================== --- trunk/connectors/translator-hive/src/test/java/org/teiid/translator/hive/TestHiveExecutionFactory.java (rev 0) +++ trunk/connectors/translator-hive/src/test/java/org/teiid/translator/hive/TestHiveExecutionFactory.java 2011-11-13 19:08:32 UTC (rev 3643) @@ -0,0 +1,168 @@ +package org.teiid.translator.hive; + +import static org.junit.Assert.assertEquals; + +import java.math.BigInteger; +import java.util.Arrays; +import java.util.List; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.mockito.Mockito; +import org.teiid.cdk.CommandBuilder; +import org.teiid.core.types.DataTypeManager; +import org.teiid.language.Command; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.metadata.Column; +import org.teiid.metadata.MetadataStore; +import org.teiid.metadata.Schema; +import org.teiid.metadata.Table; +import org.teiid.query.mapping.relational.QueryNode; +import org.teiid.query.metadata.QueryMetadataInterface; +import org.teiid.query.metadata.TransformationMetadata; +import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; +import org.teiid.translator.jdbc.TranslatedCommand; + +@SuppressWarnings("nls") +public class TestHiveExecutionFactory { + + private static HiveExecutionFactory hiveTranslator; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + private static TransformationMetadata bqt; + + @BeforeClass + public static void setUp() throws TranslatorException { + hiveTranslator = new HiveExecutionFactory(); + hiveTranslator.setUseBindVariables(false); + hiveTranslator.start(); + bqt = exampleBQT(); + } + + private void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { + Function func = LANG_FACTORY.createFunction("convert", Arrays.asList( srcExpression,LANG_FACTORY.createLiteral(tgtType, String.class)),TypeFacility.getDataTypeClass(tgtType)); + SQLConversionVisitor sqlVisitor = hiveTranslator.getSQLConversionVisitor(); + sqlVisitor.append(func); + assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, expectedExpression,sqlVisitor.toString()); + } + + private void helpTestVisitor(QueryMetadataInterface metadata, String input, String expectedOutput) throws TranslatorException { + // Convert from sql to objects + CommandBuilder commandBuilder = new CommandBuilder(metadata); + Command obj = commandBuilder.getCommand(input); + + // Convert back to SQL + TranslatedCommand tc = new TranslatedCommand(Mockito.mock(ExecutionContext.class), hiveTranslator); + tc.translateCommand(obj); + + // Check stuff + assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); //$NON-NLS-1$ + } + + @Test public void testConvertions() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.TRUE, Boolean.class), TypeFacility.RUNTIME_NAMES.BOOLEAN, "cast(true AS boolean)"); + helpTest(LANG_FACTORY.createLiteral(Byte.parseByte("123"), Byte.class), TypeFacility.RUNTIME_NAMES.BYTE, "cast(123 AS tinyint)"); + helpTest(LANG_FACTORY.createLiteral(new Integer(12345), Integer.class), TypeFacility.RUNTIME_NAMES.INTEGER, "cast(12345 AS int)"); + helpTest(LANG_FACTORY.createLiteral(Short.parseShort("1234"), Short.class), TypeFacility.RUNTIME_NAMES.SHORT, "cast(1234 AS smallint)"); + helpTest(LANG_FACTORY.createLiteral(new BigInteger("123451266182"), BigInteger.class), TypeFacility.RUNTIME_NAMES.BIG_INTEGER, "cast(123451266182 AS bigint)"); + helpTest(LANG_FACTORY.createLiteral(new String("foo-bar"), String.class), TypeFacility.RUNTIME_NAMES.STRING, "cast('foo-bar' AS string)"); + helpTest(LANG_FACTORY.createLiteral(Boolean.TRUE, Boolean.class), TypeFacility.RUNTIME_NAMES.STRING, "cast(true AS string)"); + //helpTest(LANG_FACTORY.createLiteral(new Timestamp(1320883518391L), Timestamp.class), TypeFacility.RUNTIME_NAMES.STRING, "cast('2011-11-09 18:05:18.391' AS string)"); + } + + @Test + public void testEqualityJoinCriteria() throws Exception { + String input = "SELECT A.intkey FROM BQT1.SMALLA A JOIN BQT1.SmallB B on A.intkey=B.intkey"; + String output = "SELECT A.IntKey FROM SmallA A JOIN SmallB B ON A.IntKey = B.IntKey"; + helpTestVisitor(bqt, input, output); + } + + @Test + public void testCrossJoinCriteria() throws Exception { + String input = "SELECT A.intkey FROM BQT1.SMALLA A Cross join BQT1.SmallB B"; + String output = "SELECT A.IntKey FROM SmallA A JOIN SmallB B"; + helpTestVisitor(bqt, input, output); + } + + + @Test + public void testMustHaveAliasOnView() throws Exception { + String input = "SELECT intkey FROM (select intkey from BQT1.SmallA) as X"; + String output = "SELECT X.intkey FROM (SELECT SmallA.IntKey FROM SmallA) X"; + helpTestVisitor(bqt, input, output); + } + + @Test + public void testUnionAllRewrite() throws Exception { + String input = "SELECT intkey, stringkey FROM BQT1.SmallA union all SELECT intkey, stringkey FROM BQT1.Smallb"; + String output = "SELECT intkey, stringkey FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA UNION ALL SELECT SmallB.IntKey, SmallB.StringKey FROM SmallB) X__"; + helpTestVisitor(bqt, input, output); + } + + @Test + public void testUnionAllExprRewrite() throws Exception { + String input = "SELECT count(*) as key, stringkey FROM BQT1.SmallA union all SELECT intkey, stringkey FROM BQT1.Smallb"; + String output = "SELECT key, stringkey FROM (SELECT COUNT(*) AS key, SmallA.StringKey FROM SmallA UNION ALL SELECT SmallB.IntKey, SmallB.StringKey FROM SmallB) X__"; + helpTestVisitor(bqt, input, output); + } + + @Test + public void testUnionRewrite() throws Exception { + String input = "SELECT intkey, stringkey FROM BQT1.SmallA union SELECT intkey, stringkey FROM BQT1.Smallb"; + String output = "SELECT DISTINCT intkey, stringkey FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA UNION ALL SELECT SmallB.IntKey, SmallB.StringKey FROM SmallB) X__"; + helpTestVisitor(bqt, input, output); + } + + + public static TransformationMetadata exampleBQT() { + MetadataStore metadataStore = new MetadataStore(); + Schema bqt1 = RealMetadataFactory.createPhysicalModel("BQT1", metadataStore); //$NON-NLS-1$ + Table bqt1SmallA = RealMetadataFactory.createPhysicalGroup("SmallA", bqt1); //$NON-NLS-1$ + Table bqt1SmallB = RealMetadataFactory.createPhysicalGroup("SmallB", bqt1); //$NON-NLS-1$ + String[] elemNames = new String[] { + "IntKey", "StringKey", //$NON-NLS-1$ //$NON-NLS-2$ + "IntNum", "StringNum", //$NON-NLS-1$ //$NON-NLS-2$ + "FloatNum", "LongNum", //$NON-NLS-1$ //$NON-NLS-2$ + "DoubleNum", "ByteNum", //$NON-NLS-1$ //$NON-NLS-2$ + "DateValue", "TimeValue", //$NON-NLS-1$ //$NON-NLS-2$ + "TimestampValue", "BooleanValue", //$NON-NLS-1$ //$NON-NLS-2$ + "CharValue", "ShortValue", //$NON-NLS-1$ //$NON-NLS-2$ + "BigIntegerValue", "BigDecimalValue", //$NON-NLS-1$ //$NON-NLS-2$ + "ObjectValue" }; //$NON-NLS-1$ + + String[] nativeTypes = new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, + DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, + DataTypeManager.DefaultDataTypes.FLOAT, DataTypeManager.DefaultDataTypes.BIG_INTEGER, + DataTypeManager.DefaultDataTypes.DOUBLE, DataTypeManager.DefaultDataTypes.BYTE, + DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, + DataTypeManager.DefaultDataTypes.TIMESTAMP, DataTypeManager.DefaultDataTypes.BOOLEAN, + DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.SHORT, + DataTypeManager.DefaultDataTypes.BIG_INTEGER, DataTypeManager.DefaultDataTypes.BIG_INTEGER, + DataTypeManager.DefaultDataTypes.STRING}; + + String[] runtimeTypes = new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, + DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, + DataTypeManager.DefaultDataTypes.FLOAT, DataTypeManager.DefaultDataTypes.LONG, + DataTypeManager.DefaultDataTypes.DOUBLE, DataTypeManager.DefaultDataTypes.BYTE, + DataTypeManager.DefaultDataTypes.DATE, DataTypeManager.DefaultDataTypes.TIME, + DataTypeManager.DefaultDataTypes.TIMESTAMP, DataTypeManager.DefaultDataTypes.BOOLEAN, + DataTypeManager.DefaultDataTypes.CHAR, DataTypeManager.DefaultDataTypes.SHORT, + DataTypeManager.DefaultDataTypes.BIG_INTEGER, DataTypeManager.DefaultDataTypes.BIG_DECIMAL, + DataTypeManager.DefaultDataTypes.OBJECT }; + + List<Column> bqt1SmallAe = RealMetadataFactory.createElements(bqt1SmallA, elemNames, nativeTypes); + List<Column> bqt1SmallBe = RealMetadataFactory.createElements(bqt1SmallB, elemNames, nativeTypes); + + Schema vqt = RealMetadataFactory.createVirtualModel("VQT", metadataStore); //$NON-NLS-1$ + QueryNode vqtn1 = new QueryNode("SELECT * FROM BQT1.SmallA"); //$NON-NLS-1$ + Table vqtg1 = RealMetadataFactory.createUpdatableVirtualGroup("SmallA", vqt, vqtn1); //$NON-NLS-1$ + RealMetadataFactory.createElements(vqtg1, elemNames, runtimeTypes); + + return RealMetadataFactory.createTransformationMetadata(metadataStore, "bqt");//$NON-NLS-1$ + } +} Property changes on: trunk/connectors/translator-hive/src/test/java/org/teiid/translator/hive/TestHiveExecutionFactory.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ConvertModifier.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ConvertModifier.java 2011-11-13 18:31:49 UTC (rev 3642) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ConvertModifier.java 2011-11-13 19:08:32 UTC (rev 3643) @@ -44,11 +44,20 @@ * etc. * <p>The choice of conversion logic is as follows: * <ul> - * <li>Look for a specific conversion between the source and target - {@link #addConvert(int, int, FunctionModifier)}</li> + * <li>Provide specific conversion between the source and target - {@link #addConvert(int, int, FunctionModifier)} + * mostly one do not need to provide any conversion if the default is cast(srcType AS targetType), however if the source + * database requires different specific format for example to cast(srctype, targetType FORMAT 'more-info') this conversion needs to be added</li> * <li>Filter common implicit conversions</li> * <li>Look for a general source conversion - {@link #addSourceConversion(FunctionModifier, int...)}</li> - * <li>Look for a general target conversion - {@link #addTypeConversion(FunctionModifier, int...)}</li> - * <li>Look for a type mapping, which will replace the target type with the given native type - {@link #addTypeMapping(String, int...)}</li> + * <li>Look for a general target conversion - {@link #addTypeConversion(FunctionModifier, int...)}. If the source + * database provides a specific function for converting *any* source datatype to this target datatype then use this to define it. + * Like in oracle "to_char" function will convert any other data type to string. Use this to define those kind of conversions + * convert any data type to string. so you can use this for purpose.</li> + * <li>Type maps from database data type to Teiid runtime types. - {@link #addTypeMapping(String, int...)} + * define mappings for every datatype available in database. The cast operation will replace the target type (teiid type) with the given + * native type in the cast operation generated. Do not need to really look at implicit/explicit conversions that are + * supported by the source database, because when a cast is defined on the sql it is up to the source database to apply it + * as implicit or explicit operation. Teiid generates cast always when needed</li> * <li>Drop the conversion</li> * </ul> */ Modified: trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-11-13 18:31:49 UTC (rev 3642) +++ trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-11-13 19:08:32 UTC (rev 3643) @@ -259,6 +259,11 @@ </listitem> <listitem> <para> + <emphasis>hive</emphasis> - For use with Hive database based on Hadoop + </para> + </listitem> + <listitem> + <para> <emphasis>hsql</emphasis> - for use with HSQLDB 1.7 or later. </para> </listitem>