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
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]<mailto:[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<tel:%2B91-9940395830>
From: amareshwarisr .
[mailto:[email protected]<mailto:[email protected]>]
Sent: Wednesday, June 17, 2015 5:30 PM
To: [email protected]<mailto:[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]<mailto:[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<http://lens.metastore.native.db.name>"
value="default"/>
</table_parameters>
</table_desc>
Regards,
Srinivasan Hariharan
Mob +91-9940395830<tel:%2B91-9940395830>
From: amareshwarisr .
[mailto:[email protected]<mailto:[email protected]>]
Sent: Wednesday, June 17, 2015 12:21 PM
To: [email protected]<mailto:[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]<mailto:[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<tel:%2B91-9940395830>
From: amareshwarisr .
[mailto:[email protected]<mailto:[email protected]>]
Sent: Wednesday, June 17, 2015 9:59 AM
To: [email protected]<mailto:[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<http://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]<mailto:[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<tel:%2B91-9940395830>
From: amareshwarisr .
[mailto:[email protected]<mailto:[email protected]>]
Sent: Tuesday, June 16, 2015 6:16 PM
To: [email protected]<mailto:[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]<mailto:[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<tel:%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***
