Unique Id in Dremio

1

Is there any Unique ID in Dremio like reflection id and job ID. As I checked that both changes.

Reflection ID changes when the Physical database gets updated and Job Id changes every time.

Also, I found the query ID but I can’t relate that with dataset name

2

What exactly are you trying to achieve?

Every query gets a new unique id since each run is a new job - even if the SQL is the same.

3

I want to track on reflection that fails and there count with date and time
with tableau , In sys.reflection i have datset name reflection Id but it give data only for one data i want at-least for a week , also Sys.reflection do not have the date and time …

4

You can use select * from sys.refreshes to see all refreshes.

5

but i need reflection fail count for 7 days and date and time also in Sys.reflection these information does not exist

6

@saurabh99, the sys.materializations table may provide what you need. Also, if you search for the reflection ID in the jobs page of the Dremio UI, you can examine the details of each refresh job for that reflection.

7

@saurabh99, also, the sys.refreshes table has information that may be useful to you.

8

@saurabh99

If you have access to the coordinator node, Dremio writes every query that completed successfully, failed and cancelled into a file called queries.json. We archive it and that can be changed in logback.xml

You can copy these queries.json to a filesystem based source like S3/ADLS/HDFS, add that as a source to Dremio and query the json files using Dremio

Thanks
@balaji.ramaswamy

9

Correct i have that information but in that i get the query ID . Due to which i am unable to link reflection and dataset name with it … we can not know which reflection failed we just have the query ID

10

@saurabh99, there are three IDs involved:

  • Query (job) ID
  • Reflection ID
  • Materialization ID

If you have the job/query ID, you can find the query in the Jobs page of the Dremio UI (or you can search for it in the queries.json log as @balaji.ramaswamy mentioned).

Once you find the job, the actual query string will tell you the reflection ID and the materialization ID. It will say: REFRESH REFLECTION '<reflection ID>' AS '<materialization ID>'.

For example: REFRESH REFLECTION '11c08645-1b70-4a10-a131-b5dbe111dd64' AS '48200703-5e56-4347-bf8e-db6d7a8e778e'