How to combine multiple datasets?

Elasticsearch is my data source and I want to combine all the data across all the indices. Is it possible?

There are multiple options here, and these examples may be helpful:

SELECT * from elastic.”feb*”.”big”
SELECT * from elastic.”feb,march”.”big”
SELECT * from elastic.”feb”.”big,small”

We actually have a tutorial underway explaining how this works in more detail. I’ll be sure to link to that here once it is ready.

1 Like

Thanks @kelly. It trully works. :smiley:

1 Like

I encountered a problem @kelly.

Table ‘logstash-2017.08.02,08.02.wineventlog’ not found

This is my query:

SELECT *
FROM AWSPH."logstash-2017.08.02,logstash-2017.08.03"."wineventlog"

Hi @joginar10,

I wasn’t able to reproduce your issue using a similar query with simple mappings.

Would you be able to provide us with some information about the mappings for these two indices to help us get to the root cause of your issue?

Could you also check your logs at /log/server.log and see if there are any additional errors there about this query?

Another possible cause of the issue is the Dremio ODBC driver, are you connecting Dremio to any outside clients?

Yes, that’s what i also think/cause of this problem since my two tables have different mapping although it just a minimal difference.

Below is just a part of the log since it does output a log of logs. I don’t know if what’s the reason for this.

07180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4080) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:106) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:910) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:891) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2859) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2844) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3077) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:910) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:891) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:866) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:577) ~[calcite-core-1.12.0-201707180020390814-16f46c2.jar:1.12.0-201707180020390814-16f46c2]

What do you mean by outside clients? Yes I do connect to dremio server from a remote pc. But it is only an intranet connection. Another thing is that there’s no issues from other tables when accessing remotely.

Thank you for your reply btw, @rohan. :slight_smile:

Hi @joginar10,

Two tables having different mappings would cause this issue to occur. More specifically, if the two tables share a field with the same name but with different types, they are unable to be combined and thus Dremio will and should throw an error. We’ll work on adjusting our error message to be a little bit more helpful on explaining the root cause of the issue.

Outside clients refers to BI tools such as Tableau or PowerBI which don’t seem to be an issue in this situation. :slight_smile:

Let me know if there is anything else I can do to help!

1 Like

Thanks for your reply @rohan. Gonna look forward into that. :slight_smile:

1 Like

We added a tutorial to help explain this functionality:

Hey @kelly,
for some reason I’m not able to perform SQL actions against our ElasticSearch source with ‘*’, and the error is as follows:

Can you please assist? I tried to read to tutorial few times but unfortunately no joy.
Thanks,
Lior

Hello @YakobovLior,

What version of Elasticsearch are you using?

Hey @Ben,
the ElasticSearch cluster version in 6.8.3.

Thanks,
Lior

@YakobovLior,

When you get this error message, is there a job listed for this query in the “Jobs” page?

Can you attach the profile for that job? https://www.dremio.com/tutorials/share-query-profile-dremio/

Can you also attach Dremio’s server.log? This may have more information of where the error is coming from.

Hey @ben,
attached the job profile and the relevant lines generated in server.log after executing the request.

Thanks,
Lior

8daaad12-16c2-40ab-bdd4-4337adf1fda7.zip (4.3 KB) [server.zip|attachment]
(upload://ruC7OLbWb5L4ALzegopMWaBcpF1.zip) (154 Bytes)