Can you update your jdbcdriver-site.xml conf: lens.cube.query.driver.supported.storages to include your storage name ?
On Wed, Jun 17, 2015 at 6:04 PM, Srinivasan Hariharan02 < [email protected]> wrote: > Hi, > > > > I looked into this already, unfortunately it throws below error when I > query the dimension > > > > My dimension table is department and dimtable is department_table refers > from oracle jdbc storage > > > > > > *org.apache.lens.driver.jdbc.JDBCDriver Cause :No candidate dimension > table available for department to answer the query* > > > > *Driver :org.apache.lens.driver.hive.HiveDriver Cause :No candidate > dimension table available for department to answer the query, because > {"brief":"No candidate storages for any > table","details":{"department_table":[{"cause":"NO_CANDIDATE_STORAGES","storageCauses":{"dbmsstorage":{"cause":"UNSUPPORTED"}}}]}}* > > > > *Regards,* > > *Srinivasan Hariharan* > > *Mob +91-9940395830 <%2B91-9940395830>* > > > > *From:* amareshwarisr . [mailto:[email protected]] > *Sent:* Wednesday, June 17, 2015 5:51 PM > > *To:* [email protected] > *Cc:* Vijaya Chander > *Subject:* Re: Cube Creation for Hive Tables > > > > You can query Dimension, lens would pick corresponding dimtable > accordingly and query it. > > > > You can look at example queries at > https://github.com/apache/incubator-lens/blob/master/lens-examples/src/main/resources/dimension-queries.sql > . > > > > > > > > On Wed, Jun 17, 2015 at 5:45 PM, Srinivasan Hariharan02 < > [email protected]> wrote: > > Yeah its created the dim table. Is there any way to query this dim table > directly from lens cli. > > > > *Regards,* > > *Srinivasan Hariharan* > > *Mob +91-9940395830 <%2B91-9940395830>* > > > > *From:* amareshwarisr . [mailto:[email protected]] > *Sent:* Wednesday, June 17, 2015 5:30 PM > > > *To:* [email protected] > *Cc:* Vijaya Chander > *Subject:* Re: Cube Creation for Hive Tables > > > > Skip passing table location. > > > > On Wed, Jun 17, 2015 at 5:11 PM, Srinivasan Hariharan02 < > [email protected]> wrote: > > Hi, > > > > When I creating the dim table using DBStorage I am getting the following > error message > > > > No FileSystem for scheme: jdbc > > java.io.IOException: No FileSystem for scheme: jdbc > > at > org.apache.hadoop.fs.FileSystem.getFileSystemClass(FileSystem.java:2584) > > at > org.apache.hadoop.fs.FileSystem.createFileSystem(FileSystem.java:2591) > > at org.apache.hadoop.fs.FileSystem.access$200(FileSystem.java:91) > > at > org.apache.hadoop.fs.FileSystem$Cache.getInternal(FileSystem.java:2630) > > at org.apache.hadoop.fs.FileSystem$Cache.get(FileSystem.java:2612) > > at org.apache.hadoop.fs.FileSystem.get(FileSystem.java:370) > > at org.apache.hadoop.fs.Path.getFileSystem(Path.java:296) > > at > org.apache.hadoop.hive.metastore.Warehouse.getFs(Warehouse.java:112) > > at > org.apache.hadoop.hive.metastore.Warehouse.getDnsPath(Warehouse.java) > > > > Below is the table desc tag am using in the dim table .xml > > > > <table_desc external="true" field_delimiter="," > table_location="jdbc:oracle:thin:<username>/<pwd>@<oracle-host>:1521:xe" > > storage_handler_name="org.apache.lens.storage.db.DBStorageHandler"> > > <table_parameters> > > <property name="lens.metastore.native.db.name" value="default"/> > > </table_parameters> > > </table_desc> > > > > *Regards,* > > *Srinivasan Hariharan* > > *Mob +91-9940395830 <%2B91-9940395830>* > > > > *From:* amareshwarisr . [mailto:[email protected]] > *Sent:* Wednesday, June 17, 2015 12:21 PM > > > *To:* [email protected] > *Cc:* Vijaya Chander > *Subject:* Re: Cube Creation for Hive Tables > > > > Yes. Should be possible. Example cube already has tables from HDFS and DB. > > > > On Wed, Jun 17, 2015 at 12:17 PM, Srinivasan Hariharan02 < > [email protected]> wrote: > > Thanks Amareshwari. I can do that. Currently is it possible to create a > single cube whose dimension were from Heterogonous storages like > DBStorage,HDFSStorage and HBaseStorage. > > > > *Regards,* > > *Srinivasan Hariharan* > > *Mob +91-9940395830 <%2B91-9940395830>* > > > > *From:* amareshwarisr . [mailto:[email protected]] > *Sent:* Wednesday, June 17, 2015 9:59 AM > *To:* [email protected] > *Cc:* Vijaya Chander > > > *Subject:* Re: Cube Creation for Hive Tables > > > > Hello Srinivasan, > > > > We need create HBaseStorage. Created > https://issues.apache.org/jira/browse/LENS-612 for tracking. HBaseStorage > needs to be implemented on the similar lines of > https://github.com/apache/incubator-lens/blob/master/lens-storage-db/src/main/java/org/apache/lens/storage/db/DBStorage.java > > > > Once we have HBaseStorage created, you can create your tables like the > following, with HBaseStorageHandler and its properties: > > > > Create your storage like the following ( > > > https://github.com/apache/incubator-lens/blob/master/lens-examples/src/main/resources/db-storage.xml > ) : > > > > ---- > > <x_storage classname="org.apache.lens.storage.db.DBStorage" name="mydb" > xmlns="uri:lens:cube:0.1" > > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; > xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> > > <properties/> > > </x_storage> > > ---- > > > > And tables similar to > https://github.com/apache/incubator-lens/blob/master/lens-examples/src/main/resources/city_subset.xml > > ---- > > <x_dimension_table dimension_name="city" table_name="city_subset" > weight="100.0" xmlns="uri:lens:cube:0.1" xmlns:xsi=" > http://www.w3.org/2001/XMLSchema-instance"; > xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> > > <columns> > > <column comment="ID" name="id" type="INT"/> > > <column comment="name" name="name" type="STRING"/> > > </columns> > > <storage_tables> > > <storage_table> > > <storage_name>mydb</storage_name> > > <table_desc external="true" field_delimiter="," > table_location="/tmp/db-storage.db" > > storage_handler_name="org.apache.lens.storage.db.DBStorageHandler"> > > <table_parameters> > > <property name="lens.metastore.native.db.name" value="default"/> > > </table_parameters> > > </table_desc> > > </storage_table> > > </storage_tables> > > </x_dimension_table> > > > > ---- > > > > Writing HBaseStorage class should be simple, do you want to give it a try, > as no special handling is required for HBase ? > > > > On Tue, Jun 16, 2015 at 6:52 PM, Srinivasan Hariharan02 < > [email protected]> wrote: > > Thanks Amareshwari. This partly answers my question. My need is, I have > an external dim table in hive which uses Hbase storage handler. So the > data for this table is located in Hbase, not directly under hdfs file > location. So how can I create dimension for these kind of tables in Lens > without moving the data to HDFS. > > > > *Regards,* > > *Srinivasan Hariharan* > > *Mob +91-9940395830 <%2B91-9940395830>* > > > > *From:* amareshwarisr . [mailto:[email protected]] > *Sent:* Tuesday, June 16, 2015 6:16 PM > *To:* [email protected] > *Subject:* Re: Cube Creation for Hive Tables > > > > Srinivasan Hariharan, > > > > I will try to explain how city table is created in examples. Let me see if > that helps you? > > > > If you see > https://github.com/apache/incubator-lens/blob/master/lens-examples/src/main/resources/city.xml > : > > > > ---- > > > > <x_dimension name="city" xmlns="uri:lens:cube:0.1" xmlns:xsi=" > http://www.w3.org/2001/XMLSchema-instance"; > xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> > > <attributes> > > <dim_attribute name="id" type="INT"/> > > <dim_attribute name="name" type="STRING"/> > > <dim_attribute name="POI" type="ARRAY<STRING>" > description="Point of interests"/> > > <dim_attribute name="population" type="BIGINT" /> > > </attributes> > > <properties> > > <property name="dimension.city.timed.dimension" value="dt"/> > > </properties> > > </x_dimension> > > ---- > > > > Above of definition of city defines dimension city and its attribute. This > is only logical table, not linked to any physical table. > > > > Then we have > https://github.com/apache/incubator-lens/blob/master/lens-examples/src/main/resources/city_table.xml > - Which defines the dimtable associated with dimension city > > > > ---- > > <x_dimension_table dimension_name="city" table_name="city_table" > weight="10.0" xmlns="uri:lens:cube:0.1" xmlns:xsi=" > http://www.w3.org/2001/XMLSchema-instance"; > xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd "> > > <columns> > > <column comment="ID" name="id" type="INT"/> > > <column comment="name" name="name" type="STRING"/> > > <column comment="Point of interests" name="POI" > type="ARRAY<STRING>"/> > > <column comment="city population" name="population" type="BIGINT"/> > > </columns> > > <properties> > > <property name="city.prop" value="d1"/> > > </properties> > > <storage_tables> > > <storage_table> > > <update_periods> > > <update_period>HOURLY</update_period> > > </update_periods> > > <storage_name>local</storage_name> > > <table_desc external="true" field_delimiter="," > collection_delimiter=":" table_location="/tmp/examples/city"> > > <part_cols> > > <column comment="Time column" name="dt" type="STRING"/> > > </part_cols> > > <time_part_cols>dt</time_part_cols> > > </table_desc> > > </storage_table> > > </storage_tables> > > </x_dimension_table> > > ---- > > > > Here the above definition will be mapped to table on HDFS - which is Text > formatted with delimiters specified above. > > > > Are you looking for something which uses existing tables to create facts > and dimensions - https://issues.apache.org/jira/browse/LENS-340 ? Right > now, that feature is not there. Until then you have to map lens created > table as external table to your existing table. > > > > For ex: > > You have Hive table Table1 : col1, col2; > > > > Create Dimension1 : col1, col2 as dim attributes - give schema for them. > > Create DimTable1 : col1, col2 associated with Dimension1. > > > > Then you should be good. > > > > Let us know if above answers your query. Or let us know if you are looking > for something else. > > > > Thanks > > Amareshwari > > > > > > > > On Tue, Jun 16, 2015 at 1:02 PM, Srinivasan Hariharan02 < > [email protected]> wrote: > > Hi, > > > > I have all my Dim tables in Hive, I want to create cube on top of these > tables using lens. Can anyone provide how ca I start doing that. I have > checked the examples in the resources directory under client folder. It > doesn’t help much for creation of storage dim, facts from hive tables. Help > is much appreciated. > > > > *Regards,* > > *Srinivasan Hariharan* > > *Mob +91-9940395830 <%2B91-9940395830>* > > > > > > **************** CAUTION - Disclaimer ***************** > > This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely > > for the use of the addressee(s). If you are not the intended recipient, please > > notify the sender by e-mail and delete the original message. Further, you are > not > > to copy, disclose, or distribute this e-mail or its contents to any other > person and > > any such actions are unlawful. This e-mail may contain viruses. Infosys has > taken > > every reasonable precaution to minimize this risk, but is not liable for any > damage > > you may sustain as a result of any virus in this e-mail. You should carry out > your > > own virus checks before opening the e-mail or attachment. Infosys reserves the > > right to monitor and review the content of all messages sent to or from this > e-mail > > address. Messages sent to or from this e-mail address may be stored on the > > Infosys e-mail system. > > ***INFOSYS******** End of Disclaimer ********INFOSYS*** > > > > > > > > >
