lens-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "amareshwarisr ." <amareshw...@gmail.com>
Subject Re: Cube Creation for Hive Tables
Date Wed, 17 Jun 2015 15:51:31 GMT
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 <arshad.matin@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&lt;STRING&gt;"
>> 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&lt;STRING&gt;"/>
>>
>>     <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.
>

Mime
View raw message