Hi Amruth Thank you for your help. Yeah, the "*select col_2 from center.test limit 100*" returns the right result. But "*select col_2 from center.test*" returns nothing. I also check the es driver configuration file, and find " *lens.driver.es.max.row.size*" is set to -1 . ps: My elasticsearch version is 2.3.2. My Lens version is 2.5.0-beta
The Lens server configuration file : > <property> > > <name>lens.driver.es.term.fetch.size</name> > > <description>Fetch (buffer) size for document look up >> queries</description> > > <value>10000</value> > > </property> > > <property> > > <name>lens.driver.es.query.timeout.millis</name> > > <description>Query timeout</description> > > <value>10000</value> > > </property> > > <property> > > * <name>lens.driver.es.max.row.size</name>* > > * <description>max rows for es document look up queries, non existent >> or -1 refers no limit</description>* > > * <value>-1</value>* > > </property> > > <property> > > <name>lens.driver.es.aggr.bucket.size</name> > > <description>Max cardinality of group by (higher value means higher >> resource usage at server end)</description> > > <value>6</value> > > </property> > > <property> > > <name>lens.driver.es.jest.servers</name> > > <description>List of http servers, will be used on a round robin >> basis</description> > > <value>http://10.10.44.21:19200</value> > > </property> > > <property> > > <name>lens.driver.es.jest.max.conn</name> > > <description>max connections</description> > > <value>20</value> > > </property> > > <property> > > <name>lens.driver.es.client.class</name> > > <description>Choice of client class, default is >> JestClientImpl</description> > > <value>org.apache.lens.driver.es.client.jest.JestClientImpl</value> > > </property> > > </configuration> > > the command result is as follow: > lens-shell>*select col_2 from center.test limit 100* > > 06 七月 2016 05:39:28 [Spring Shell] INFO cliLogger - Query handle: >> 1c602a46-118c-45fb-be93-ad62717f5d45 > > 06 七月 2016 05:39:28 [Spring Shell] INFO cliLogger - User query: 'select >> col_2 from center.test limit 100' was submitted to es/es1 > > 06 七月 2016 05:39:28 [Spring Shell] INFO cliLogger - Driver query: >> 'select col_2 from center.test limit 100' and Driver handle: null > > 06 七月 2016 05:39:28 [Spring Shell] INFO cliLogger - Query Status: Status >> : LAUNCHED > > Progress : 0.0 > > > > 06 七月 2016 05:39:28 [Spring Shell] INFO cliLogger - Query Status: Status >> : SUCCESSFUL > > Message : Query is successful! > > Progress : 1.0 > > Result Available > > col_2 > > *Result available in memory, attaching here: * > > >> *5411.0 * > > *5433.0 * > > *5678.0 * > > *3 rows processed in (0) seconds.* > > lens-shell>select col_2 from center.test 06 七月 2016 05:41:38 [Spring Shell] INFO cliLogger - Query handle: > df5be094-69a2-4cc7-b8d3-bd6245e63472 06 七月 2016 05:41:38 [Spring Shell] INFO cliLogger - User query: 'select > col_2 from center.test' was submitted to es/es1 06 七月 2016 05:41:38 [Spring Shell] INFO cliLogger - Driver query: 'select > col_2 from center.test' and Driver handle: null *col_2 * *Result available in memory, attaching here: * > *0 rows processed in (0) seconds.* > *lens-shell>* Thanks Gary On Wed, Jul 6, 2016 at 1:00 PM, Amruth Sampath <[email protected]> wrote: > Hi Gary, Yes * is also not supported just yet in case of elastic search > driver. > > I could see that you are getting the results successfully for the > aggregation query : > > *col_2 * > > *Result available in memory, attaching here: * > > >> *16522.0 * > > > I am not sure whats the problem with this case though "*select col_2 > from center.test". *It should have given you results limited to > lens.driver.es.max.row.size. > > Ill try to debug this in the evening. Can you put down the versions of > lens and elastic search you are using, Ill try to reproduce. > > Meanwhile can you try adding a limit clause and check if you are getting > the result. > *select col_2 from center.test limit 100* > > Thanks, > > Thanks, > Amruth S > (09486075517) > > On Wed, Jul 6, 2016 at 10:16 AM, Gary Wu <[email protected]> > wrote: > >> involve all. >> >> Hi Amruth and Amareshwarisr >> Thank you for your reply. Lens is a great software, the new querying >> engine is very cool. :) >> Follow your instruction, I run a client-cli and input some commands in >> the console. >> I found some confusing test results: >> 1) the aggregation operation(sum/ count/ max) could return a result. but >> select *returns nothing*. Also I find * is not supported too. >> Dose I neglect anything which is important? >> >> lens-shell>*select sum(col_2) from center.test* >>> >>> 06 七月 2016 03:56:42 [Spring Shell] INFO cliLogger - Query handle: >>>> 55d39cb3-1e34-419c-956e-27d537fcba66 >>> >>> 06 七月 2016 03:56:42 [Spring Shell] INFO cliLogger - User query: 'select >>>> sum(col_2) from center.test' was submitted to es/es1 >>> >>> 06 七月 2016 03:56:42 [Spring Shell] INFO cliLogger - Driver query: >>>> 'select sum(col_2) from center.test' and Driver handle: null >>> >>> 06 七月 2016 03:56:42 [Spring Shell] INFO cliLogger - Query Status: >>>> Status : LAUNCHED >>> >>> Progress : 0.0 >>> >>> >>> >>> 06 七月 2016 03:56:42 [Spring Shell] INFO cliLogger - Query Status: >>>> Status : SUCCESSFUL >>> >>> Message : Query is successful! >>> >>> Progress : 1.0 >>> >>> Result Available >>> >>> *col_2 * >>> >>> *Result available in memory, attaching here: * >>> >>> >>>> *16522.0 * >>> >>> *1 rows processed in (0) seconds* >>> >>> >> >> lens-shell>*select col_2 from center.test* >> >> 06 七月 2016 04:02:22 [Spring Shell] INFO cliLogger - Query handle: >>> 00c29269-67d0-4fcb-af69-0b3152a5e0ba >> >> 06 七月 2016 04:02:22 [Spring Shell] INFO cliLogger - User query: 'select >>> col_2 from center.test' was submitted to es/es1 >> >> 06 七月 2016 04:02:22 [Spring Shell] INFO cliLogger - Driver query: >>> 'select col_2 from center.test' and Driver handle: null >> >> 06 七月 2016 04:02:22 [Spring Shell] INFO cliLogger - Query Status: Status >>> : SUCCESSFUL >> >> Message : Query is successful! >> >> Progress : 1.0 >> >> Result Available >> >> *col_2 * >> >> *Result available in memory, attaching here: * >> >> >>> *0 rows processed in (0) seconds.* >> >> >> 2) all the commands was submitted to the new ES(showed in the log) >> automatically, though I didn't point out where the the table is . >> I have several data sources, hive jdbc and es .etc . So I guess the Lens >> will try all available data sources( or store the meta data), and then find >> ES has the table, then Lens submits the request to the es. Is it right? >> >> 06 七月 2016 03:56:42 [Spring Shell] INFO cliLogger - User query: 'select >>> sum(col_2) from center.test' was submitted to es/es1 >> >> >> Thanks >> >> On Wed, Jul 6, 2016 at 12:07 PM, Amruth Sampath <[email protected]> >> wrote: >> >>> Gary, >>> >>> The general format is >>> >>> *select <col1>, <col2> ... from <index.type>;* >>> >>> Join is not supported. Basic aggregations, group by's are supported >>> >>> In your case probably, >>> >>> *select col1, col2 from plant.flower;* >>> >>> >>> Thanks, >>> Amruth S >>> (09486075517) >>> >>> On Wed, Jul 6, 2016 at 8:18 AM, amareshwarisr . <[email protected]> >>> wrote: >>> >>>> Gary, >>>> >>>> I think you should be able to query es index simply with SQL, no need >>>> to create any dimtable or fact table in lens, unless they are part of a >>>> cube you are trying. >>>> >>>> Amruth, Can you help Gary on running queries on elastic search? >>>> >>>> Thanks >>>> >>>> >>>> On Tue, Jul 5, 2016 at 5:54 PM, Gary Wu <[email protected]> >>>> wrote: >>>> >>>>> Hi Team, >>>>> I am trying to use *elasticsearch *as the data source for lens. I do >>>>> as follow: >>>>> 1) I make a new directory in driver dir and add a xml. >>>>> >>>>>> ..../server/conf/drivers/ >>>>>> >>>>>> >>>>>> *├── es│ └── es1│ └── esdriver-site.xml* >>>>>> ├── hive >>>>>> │ └── hive1 >>>>>> │ └── hivedriver-site.xml >>>>>> └── jdbc >>>>>> └── jdbc1 >>>>>> └── jdbcdriver-site.xml >>>>> >>>>> >>>>> <?xml version="1.0"?> >>>>>>> >>>>>> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?> >>>>> >>>>> <configuration> >>>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.driver.es.term.fetch.size</name> >>>>>> >>>>>> <description>Fetch (buffer) size for document look up >>>>>>> queries</description> >>>>>> >>>>>> <value>10000</value> >>>>>> >>>>>> </property> >>>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.driver.es.query.timeout.millis</name> >>>>>> >>>>>> <description>Query timeout</description> >>>>>> >>>>>> <value>10000</value> >>>>>> >>>>>> </property> >>>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.driver.es.max.row.size</name> >>>>>> >>>>>> <description>max rows for es document look up queries, non >>>>>>> existent or -1 refers no limit</description> >>>>>> >>>>>> <value>-1</value> >>>>>> >>>>>> </property> >>>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.driver.es.aggr.bucket.size</name> >>>>>> >>>>>> <description>Max cardinality of group by (higher value means >>>>>>> higher resource usage at server end)</description> >>>>>> >>>>>> <value>6</value> >>>>>> >>>>>> </property> >>>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.driver.es.jest.servers</name> >>>>>> >>>>>> <description>List of http servers, will be used on a round robin >>>>>>> basis</description> >>>>>> >>>>>> <value>*http://10.10.44.21:19200 <http://10.10.44.21:19200>* >>>>>>> </value> >>>>>> >>>>>> </property> >>>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.driver.es.jest.max.conn</name> >>>>>> >>>>>> <description>max connections</description> >>>>>> >>>>>> <value>20</value> >>>>>> >>>>>> </property> >>>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.driver.es.client.class</name> >>>>>> >>>>>> <description>Choice of client class, default is >>>>>>> JestClientImpl</description> >>>>>> >>>>>> >>>>>>> <value>org.apache.lens.driver.es.client.jest.JestClientImpl</value> >>>>>> >>>>>> </property> >>>>>> >>>>>> </configuration> >>>>>> >>>>>> >>>>> 2) Then I edit a driver item in lens-site.xml by >>>>> >>>>>> <property> >>>>>> >>>>>> <name>lens.server.drivers</name> >>>>>> >>>>>> >>>>>>> >>>>>>> <value>hive:org.apache.lens.driver.hive.HiveDriver,jdbc:org.apache.lens.driver.jdbc.JDBCDriver, >>>>>>> *es:org.apache.lens.driver.es.ESDriver*</value> >>>>>> >>>>>> </property> >>>>>> >>>>>> >>>>> In my elasticsearch (http://10.10.44.21:19200), there is *already *a >>>>> doc,named >>>>> index/type/index .... >>>>> plant/flower/1 .... >>>>> plant/flower/2 .... >>>>> plant/flower/3 .... >>>>> >>>>> *What should I do next for querying the data from es *? add dim or >>>>> dimtable ? I did not find examples for elasticsearch in lens document. >>>>> Could you give me some instructions or some examples for that ? >>>>> >>>>> Thank you very much. >>>>> Gary >>>>> >>>> >>>> >>> >> >
