ask a question about lens join two tables on different storages

["Wangchangchun (A)" <wa...@huawei.com>]

HI, can anyone answer me two questions?

1.
IN "run-examples.sh", the queries that is "dimension-queries.sql" and "cube-queries.sql",
Is there a sql will execute on jdbc and also on hive, and then join the results of them. I want to use a case to track the code implemention.

2.
Whether lens support some native tables in jdbc-compiant database, some other native tables in hive, and a sql will join the results of these two tables?

Re: ask a question about lens join two tables on different storages

[Rajat Khandelwal <pr...@apache.org>]

>
> Some native tables have “local” prefix such as local_city_table, are they
> on local file system or on hdfs system?
>
The prefix is according to the naming convention:
http://lens.apache.org/user/olap-cube.html#Storage_table


> If they are on hdfs system, but I can not find them through “ hadoop fs
> –ls command” command.
>
>  As for where they are, they are on the hive metastore. If you're running
in default setup, look for property hive.metastore.warehouse.dir in lens
server's lens-site.xml. That is where the metastore should be.

>
>
> Another strange thing is , lens use “INSERT OVERWRITE DIRECTORY” to write
> results to hdfs , not use “INSERT OVERWRITE local DIRECTORY”, but why
> result is on local file system not on hdfs?
>
> Look at ResultFormatter. As soon as result is available on hdfs, it is
formatted and made available on local fs.

答复: ask a question about lens join two tables on different storages

["Wangchangchun (A)" <wa...@huawei.com>]

Thanks, I understand.

Some native tables have “local” prefix such as local_city_table, are they on local file system or on hdfs system?
If they are on hdfs system, but I can not find them through “ hadoop fs –ls command” command.

Another strange thing is , lens use “INSERT OVERWRITE DIRECTORY” to write results to hdfs , not use “INSERT OVERWRITE local DIRECTORY”, but why result is on local file system not on hdfs?

lens-shell> query execute select * from local_city_table
25 Aug 2015 16:19:48,359 [Spring Shell] INFO  cliLogger  - Query handle: 87f0b49c-3757-458d-bf09-64b0fc1094ee
25 Aug 2015 16:19:52,443 [Spring Shell] INFO  cliLogger  - User query: 'select * from local_city_table' was submitted to org.apache.lens.driver.hive.HiveDriver
25 Aug 2015 16:19:52,443 [Spring Shell] INFO  cliLogger  -  Driver query: 'INSERT OVERWRITE DIRECTORY "file:/tmp/lensreports/hdfsout/87f0b49c-3757-458d-bf09-64b0fc1094ee"  select * from local_city_table ' and Driver handle: OperationHandle [opType=EXECUTE_STATEMENT, getHandleIdentifier()=ab9a2df9-e6e3-413b-8c12-bf185ebeb400]




发件人: amareshwarisr . [mailto:amareshwari@gmail.com]
发送时间: 2015年8月25日 15:11
收件人: user@lens.apache.org
抄送: user@lens.incubator.apache.org
主题: Re: ask a question about lens join two tables on different storages

Hello Wangchangchun,

On Tue, Aug 25, 2015 at 12:34 PM, Wangchangchun (A) <wa...@huawei.com>> wrote:
HI, can anyone answer me two questions?
1.
IN “run-examples.sh”, the queries that is “dimension-queries.sql” and “cube-queries.sql”,
Is there a sql will execute on jdbc and also on hive, and then join the results of them. I want to use a case to track the code implemention.
run-examples.sh would run fine out of the box  with default setup, unless you modified hivedriver-site.xml and jdbcdriver-site.xml to point to some thing else.

run-examples.sh runqueries will run all queries in dimension-queries.sql and cube-queries.sql. You have to run
run-examples.sh sample-metastore
run-examples.sh populate-metastore

before running runqueries.


2.
Whether lens support some native tables in jdbc-compiant database, some other native tables in hive, and a sql will join the results of these two tables?
Lens supports native table queries in jdbc-complaint database and also other native table queries in hive. But you cannot join the results from jdbc and hive.

Thanks
Amareshwari

Re: ask a question about lens join two tables on different storages

["amareshwarisr ." <am...@gmail.com>]

Hello Wangchangchun,

On Tue, Aug 25, 2015 at 12:34 PM, Wangchangchun (A) <
wangchangchun88@huawei.com> wrote:

> HI, can anyone answer me two questions?
>
> 1.
>
> IN “run-examples.sh”, the queries that is “dimension-queries.sql” and
> “cube-queries.sql”,
>
> Is there a sql will execute on jdbc and also on hive, and then join the
> results of them. I want to use a case to track the code implemention.
>
> run-examples.sh would run fine out of the box  with default setup, unless
you modified hivedriver-site.xml and jdbcdriver-site.xml to point to some
thing else.

run-examples.sh runqueries will run all queries in dimension-queries.sql
and cube-queries.sql. You have to run
run-examples.sh sample-metastore
run-examples.sh populate-metastore

before running runqueries.


>
> 2.
>
> Whether lens support some native tables in jdbc-compiant database, some
> other native tables in hive, and a sql will join the results of these two
> tables?
>
Lens supports native table queries in jdbc-complaint database and also
other native table queries in hive. But you cannot join the results from
jdbc and hive.

Thanks
Amareshwari

Re: ask a question about lens join two tables on different storages

[Rajat Khandelwal <pr...@apache.org>]

Lens doesn't run queries across execution engines. One query will be fully
run on one execution engine, whether it be hive or jdbc or something else.
The scenarios mentioned are not supported.

I hope both the questions are answered.