You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@lens.apache.org by Srinivasan Hariharan02 <Sr...@infosys.com> on 2015/06/16 09:32:29 UTC
Cube Creation for Hive Tables
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
RE: Cube Creation for Hive Tables
Posted by Srinivasan Hariharan02 <Sr...@infosys.com>.
I couldn’t try today I will let you know by tomorrow.
Regards,
Srinivasan Hariharan
Mob +91-9940395830
From: amareshwarisr . [mailto:amareshwari@gmail.com]
Sent: Wednesday, June 17, 2015 9:22 PM
To: user@lens.incubator.apache.org
Cc: Vijaya Chander
Subject: Re: Cube Creation for Hive Tables
Srinivasan,
Try giving :
lens.metastore.native.db.name<http://lens.metastore.native.db.name/> and lens.metastore.native.table.name<http://lens.metastore.native.db.name/> properties with underlying db name and table name.
Also, did you give jdbc url for oracle in the driver ?
On Wed, Jun 17, 2015 at 9:14 PM, Arshad Matin <ar...@inmobi.com>> wrote:
can you paste the query and logs?
On Wed, Jun 17, 2015 at 6:30 PM, Srinivasan Hariharan02 <Sr...@infosys.com>> wrote:
I changed jdbcdriver-site.xml
But getting the below error
Launching query failed cause:Driver :org.apache.lens.driver.jdbc.JDBCDriver Cause :user lacks privilege or object not found:
But table is present in the oracle, Should there any naming convention for these tables in jdbc databases???
Regards,
Srinivasan Hariharan
Mob +91-9940395830<tel:%2B91-9940395830>
From: amareshwarisr . [mailto:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 6:09 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
Cc: Vijaya Chander
Subject: Re: Cube Creation for Hive Tables
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 <Sr...@infosys.com>> 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<tel:%2B91-9940395830>
From: amareshwarisr . [mailto:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 5:51 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 5:30 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 12:21 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 9:59 AM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Tuesday, June 16, 2015 6:16 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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***
_____________________________________________________________
The information contained in this communication is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. It may contain confidential or legally privileged information. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. The firm is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt.
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
Srinivasan,
Try giving :
lens.metastore.native.db.name and lens.metastore.native.table.name
<http://lens.metastore.native.db.name/> properties with underlying db name
and table name.
Also, did you give jdbc url for oracle in the driver ?
On Wed, Jun 17, 2015 at 9:14 PM, Arshad Matin <ar...@inmobi.com>
wrote:
> can you paste the query and logs?
>
> On Wed, Jun 17, 2015 at 6:30 PM, Srinivasan Hariharan02 <
> Srinivasan_H02@infosys.com> wrote:
>
>> I changed jdbcdriver-site.xml
>>
>>
>>
>> But getting the below error
>>
>>
>>
>> Launching query failed cause:Driver
>> :org.apache.lens.driver.jdbc.JDBCDriver Cause :user lacks privilege or
>> object not found:
>>
>>
>>
>> But table is present in the oracle, Should there any naming convention
>> for these tables in jdbc databases???
>>
>>
>>
>> *Regards,*
>>
>> *Srinivasan Hariharan*
>>
>> *Mob +91-9940395830 <%2B91-9940395830>*
>>
>>
>>
>> *From:* amareshwarisr . [mailto:amareshwari@gmail.com]
>> *Sent:* Wednesday, June 17, 2015 6:09 PM
>>
>> *To:* user@lens.incubator.apache.org
>> *Cc:* Vijaya Chander
>> *Subject:* Re: Cube Creation for Hive Tables
>>
>>
>>
>> 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 <
>> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
>> *Sent:* Wednesday, June 17, 2015 5:51 PM
>>
>>
>> *To:* user@lens.incubator.apache.org
>> *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 <
>> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
>> *Sent:* Wednesday, June 17, 2015 5:30 PM
>>
>>
>> *To:* user@lens.incubator.apache.org
>> *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 <
>> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
>> *Sent:* Wednesday, June 17, 2015 12:21 PM
>>
>>
>> *To:* user@lens.incubator.apache.org
>> *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 <
>> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
>> *Sent:* Wednesday, June 17, 2015 9:59 AM
>> *To:* user@lens.incubator.apache.org
>> *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 <
>> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
>> *Sent:* Tuesday, June 16, 2015 6:16 PM
>> *To:* user@lens.incubator.apache.org
>> *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 <
>> Srinivasan_H02@infosys.com> 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***
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
> _____________________________________________________________
> The information contained in this communication is intended solely for the
> use of the individual or entity to whom it is addressed and others
> authorized to receive it. It may contain confidential or legally privileged
> information. If you are not the intended recipient you are hereby notified
> that any disclosure, copying, distribution or taking any action in reliance
> on the contents of this information is strictly prohibited and may be
> unlawful. If you have received this communication in error, please notify
> us immediately by responding to this email and then delete it from your
> system. The firm is neither liable for the proper and complete transmission
> of the information contained in this communication nor for any delay in its
> receipt.
>
Re: Cube Creation for Hive Tables
Posted by Arshad Matin <ar...@inmobi.com>.
can you paste the query and logs?
On Wed, Jun 17, 2015 at 6:30 PM, Srinivasan Hariharan02 <
Srinivasan_H02@infosys.com> wrote:
> I changed jdbcdriver-site.xml
>
>
>
> But getting the below error
>
>
>
> Launching query failed cause:Driver
> :org.apache.lens.driver.jdbc.JDBCDriver Cause :user lacks privilege or
> object not found:
>
>
>
> But table is present in the oracle, Should there any naming convention for
> these tables in jdbc databases???
>
>
>
> *Regards,*
>
> *Srinivasan Hariharan*
>
> *Mob +91-9940395830*
>
>
>
> *From:* amareshwarisr . [mailto:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 6:09 PM
>
> *To:* user@lens.incubator.apache.org
> *Cc:* Vijaya Chander
> *Subject:* Re: Cube Creation for Hive Tables
>
>
>
> 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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 5:51 PM
>
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 5:30 PM
>
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 12:21 PM
>
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 9:59 AM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Tuesday, June 16, 2015 6:16 PM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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***
>
>
>
>
>
>
>
>
>
>
>
--
_____________________________________________________________
The information contained in this communication is intended solely for the
use of the individual or entity to whom it is addressed and others
authorized to receive it. It may contain confidential or legally privileged
information. If you are not the intended recipient you are hereby notified
that any disclosure, copying, distribution or taking any action in reliance
on the contents of this information is strictly prohibited and may be
unlawful. If you have received this communication in error, please notify
us immediately by responding to this email and then delete it from your
system. The firm is neither liable for the proper and complete transmission
of the information contained in this communication nor for any delay in its
receipt.
RE: Cube Creation for Hive Tables
Posted by Srinivasan Hariharan02 <Sr...@infosys.com>.
I changed jdbcdriver-site.xml
But getting the below error
Launching query failed cause:Driver :org.apache.lens.driver.jdbc.JDBCDriver Cause :user lacks privilege or object not found:
But table is present in the oracle, Should there any naming convention for these tables in jdbc databases???
Regards,
Srinivasan Hariharan
Mob +91-9940395830
From: amareshwarisr . [mailto:amareshwari@gmail.com]
Sent: Wednesday, June 17, 2015 6:09 PM
To: user@lens.incubator.apache.org
Cc: Vijaya Chander
Subject: Re: Cube Creation for Hive Tables
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 <Sr...@infosys.com>> 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<tel:%2B91-9940395830>
From: amareshwarisr . [mailto:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 5:51 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 5:30 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 12:21 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 9:59 AM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Tuesday, June 16, 2015 6:16 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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***
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
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 <
Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 5:51 PM
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 5:30 PM
>
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 12:21 PM
>
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 9:59 AM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Tuesday, June 16, 2015 6:16 PM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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***
>
>
>
>
>
>
>
>
>
RE: Cube Creation for Hive Tables
Posted by Srinivasan Hariharan02 <Sr...@infosys.com>.
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:amareshwari@gmail.com]
Sent: Wednesday, June 17, 2015 5:51 PM
To: user@lens.incubator.apache.org
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 5:30 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 12:21 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 9:59 AM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Tuesday, June 16, 2015 6:16 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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***
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
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 <
Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 5:30 PM
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 12:21 PM
>
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 9:59 AM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Tuesday, June 16, 2015 6:16 PM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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***
>
>
>
>
>
>
>
RE: Cube Creation for Hive Tables
Posted by Srinivasan Hariharan02 <Sr...@infosys.com>.
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
From: amareshwarisr . [mailto:amareshwari@gmail.com]
Sent: Wednesday, June 17, 2015 5:30 PM
To: user@lens.incubator.apache.org
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 12:21 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 9:59 AM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Tuesday, June 16, 2015 6:16 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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***
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
Skip passing table location.
On Wed, Jun 17, 2015 at 5:11 PM, Srinivasan Hariharan02 <
Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 12:21 PM
>
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 9:59 AM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Tuesday, June 16, 2015 6:16 PM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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***
>
>
>
>
>
RE: Cube Creation for Hive Tables
Posted by Srinivasan Hariharan02 <Sr...@infosys.com>.
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
From: amareshwarisr . [mailto:amareshwari@gmail.com]
Sent: Wednesday, June 17, 2015 12:21 PM
To: user@lens.incubator.apache.org
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Wednesday, June 17, 2015 9:59 AM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Tuesday, June 16, 2015 6:16 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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***
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
Yes. Should be possible. Example cube already has tables from HDFS and DB.
On Wed, Jun 17, 2015 at 12:17 PM, Srinivasan Hariharan02 <
Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Wednesday, June 17, 2015 9:59 AM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Tuesday, June 16, 2015 6:16 PM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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***
>
>
>
RE: Cube Creation for Hive Tables
Posted by Srinivasan Hariharan02 <Sr...@infosys.com>.
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
From: amareshwarisr . [mailto:amareshwari@gmail.com]
Sent: Wednesday, June 17, 2015 9:59 AM
To: user@lens.incubator.apache.org
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 <Sr...@infosys.com>> 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:amareshwari@gmail.com<ma...@gmail.com>]
Sent: Tuesday, June 16, 2015 6:16 PM
To: user@lens.incubator.apache.org<ma...@lens.incubator.apache.org>
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 <Sr...@infosys.com>> 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***
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
No, we already have DB storage.
On Wed, Jun 17, 2015 at 11:51 AM, Himanshu Gahlaut <
himanshu.gahlaut@inmobi.com> wrote:
> On similar lines, Will we have to write Postgres storage, Oracle storage,
> MySql storage class, when we want to use these storages with lens ?
>
> Regards,
> Himanshu
>
> On Wednesday, June 17, 2015, amareshwarisr . <am...@gmail.com>
> wrote:
>
>> 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 <
>> Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
>>> *Sent:* Tuesday, June 16, 2015 6:16 PM
>>> *To:* user@lens.incubator.apache.org
>>> *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 <
>>> Srinivasan_H02@infosys.com> 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***
>>>
>>>
>>
> _____________________________________________________________
> The information contained in this communication is intended solely for the
> use of the individual or entity to whom it is addressed and others
> authorized to receive it. It may contain confidential or legally privileged
> information. If you are not the intended recipient you are hereby notified
> that any disclosure, copying, distribution or taking any action in reliance
> on the contents of this information is strictly prohibited and may be
> unlawful. If you have received this communication in error, please notify
> us immediately by responding to this email and then delete it from your
> system. The firm is neither liable for the proper and complete transmission
> of the information contained in this communication nor for any delay in its
> receipt.
Re: Cube Creation for Hive Tables
Posted by Himanshu Gahlaut <hi...@inmobi.com>.
On similar lines, Will we have to write Postgres storage, Oracle storage,
MySql storage class, when we want to use these storages with lens ?
Regards,
Himanshu
On Wednesday, June 17, 2015, amareshwarisr . <am...@gmail.com> wrote:
> 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 <
> Srinivasan_H02@infosys.com
> <javascript:_e(%7B%7D,'cvml','Srinivasan_H02@infosys.com');>> 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:amareshwari@gmail.com
>> <javascript:_e(%7B%7D,'cvml','amareshwari@gmail.com');>]
>> *Sent:* Tuesday, June 16, 2015 6:16 PM
>> *To:* user@lens.incubator.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@lens.incubator.apache.org');>
>> *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 <
>> Srinivasan_H02@infosys.com
>> <javascript:_e(%7B%7D,'cvml','Srinivasan_H02@infosys.com');>> 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***
>>
>>
>
--
_____________________________________________________________
The information contained in this communication is intended solely for the
use of the individual or entity to whom it is addressed and others
authorized to receive it. It may contain confidential or legally privileged
information. If you are not the intended recipient you are hereby notified
that any disclosure, copying, distribution or taking any action in reliance
on the contents of this information is strictly prohibited and may be
unlawful. If you have received this communication in error, please notify
us immediately by responding to this email and then delete it from your
system. The firm is neither liable for the proper and complete transmission
of the information contained in this communication nor for any delay in its
receipt.
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
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 <
Srinivasan_H02@infosys.com> 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:amareshwari@gmail.com]
> *Sent:* Tuesday, June 16, 2015 6:16 PM
> *To:* user@lens.incubator.apache.org
> *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 <
> Srinivasan_H02@infosys.com> 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***
>
>
RE: Cube Creation for Hive Tables
Posted by Srinivasan Hariharan02 <Sr...@infosys.com>.
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
From: amareshwarisr . [mailto:amareshwari@gmail.com]
Sent: Tuesday, June 16, 2015 6:16 PM
To: user@lens.incubator.apache.org
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 <Sr...@infosys.com>> 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***
Re: Cube Creation for Hive Tables
Posted by "amareshwarisr ." <am...@gmail.com>.
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 <
Srinivasan_H02@infosys.com> 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>*
>
>
>