Apache Druid

as far as I know JDBC is the best option for connectivity of third-party stuff

Thank you. Actually I would like to connect PowerBI Desktop to Apache Druid, and there is no JDBC connection for PowerBI Desktop.

ah, that's a common request, although I am not aware of a way to do it using DirectQuery at this time

it can be done with an import, although of course that means the dataset you're working with must be relatively small in size (perhaps the result of a query that aggregates or filters data such that it's relatively small)

first thing i would consider is: do you need to actually store the theta sketches? if you have the input stored as its own column, you can compute the sketches at query time.

you only need to store sketches if you're using rollup and are also not retaining the field that would be the input to the sketch

if you do determine that you need to store them, i'm not sure if you'd be able to squish it below an avg of 8 bytes. i think that's about as small as one can get given the preamble

Well, we do not to store the fields used as input to sketches, so we definitely need to store the sketches.

My understanding is that P3M is three calendar months. Current month, last month and the one before

it's based on the current date, so if you set this rule then all of your data would be dropped (as it is all older than 3 months from the current date)

Thanks for replies! So it always consider the current date which is today regardless what segment's end date is. Right? :slightly_smiling_face:

If I would like to keep the last 3 months data in data source considering the segment's end date going back, How would be my data retention policies look like?

hmm. I don't think we have an automatic one for that. a clunky idea: you could use `loadByInterval` with a specific 3 month interval, and update it from time to time

(by API, presumably, so you don't need to click the button every day)

ah alright, maybe doing it via Airflow DAG or something? scheduling the API requests periodically?

In Druid I don't know of any concept of grouping datasources into a "schema".

But I can think of one or two ways to set up datasources (tables) in a hierarchical manner, e.g.:

• ABC_parent_table (parent_id, parent_attr ...)
• ABC_child_table (parent_id, child_id, child_attr, ...)
which would imply a join between the two ...

Is this along the lines of what you are thinking?

Hi <@U04C4593VDY> thanks for the answer
No it's not what I'm looking for, Actually I'm looking for creating a structure in such manner "countryA"."table1" "countryA"."table2", "countryB"."tablex" so tables 1 and 2 belong to countryA and tablex belongs to countryB

Yeah, that's along the lines of a single tenant database design ... the most common way I've seen done is by prefixing the table names, e.g. countryA_table1, etc ...

```# Druid basic security
druid.auth.authenticatorChain=["MyBasicMetadataAuthenticator"]
druid.auth.authenticator.MyBasicMetadataAuthenticator.type=basic

# Default password for 'admin' user, should be changed for production.
druid.auth.authenticator.MyBasicMetadataAuthenticator.initialAdminPassword=password1

# Default password for internal 'druid_system' user, should be changed for production.
druid.auth.authenticator.MyBasicMetadataAuthenticator.initialInternalClientPassword=password2

# Uses the metadata store for storing users, you can use authentication API to create new users and grant permissions
druid.auth.authenticator.MyBasicMetadataAuthenticator.credentialsValidator.type=metadata

# If true and the request credential doesn't exists in this credentials store, the request will proceed to next Authenticator in the chain.
druid.auth.authenticator.MyBasicMetadataAuthenticator.skipOnFailure=false
druid.auth.authenticator.MyBasicMetadataAuthenticator.authorizerName=MyBasicMetadataAuthorizer```
using this along with extension

first thing i would check is whether your Coordinator is healthy? if it is healthy then next i would check to make sure the same config is present on all servers

Please try sample configs from <https://gist.github.com/davidagee/c0c839cd23f047b838e8a3ea73320346> looks like few configs are missing as per  pasted configs

i needed to add escalator configs also then it worked

here are some basic security examples
Managing Users
Listing users
Check for current users to be authenticated using basic security:
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authentication/db/basic/users>

["admin","John"]```
Create user
Create users needed using an API similar to the following:
```curl -XPOST -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authentication/db/basic/users/john>```
Set user password
```curl -XPOST -H'Content-Type: application/json' -d @password.json -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authentication/db/{authenticatorName}/users/john/credentials>```
password.json:
{
  "password": "Imply-123"
}
Delete user
```curl -XDELETE -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authentication/db/basic/users/john>```
Describe user
Short Authentication Description
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authentication/db/basic/users/admin>

{"name":"admin","credentials":{"salt":"aUYFxrSb1PuyklsI/WUg629HxOi73cbl7vrehzZ13Ik=","hash":"3la9loHU1NgEcJHiUcp6BzqcXvhqZxspMegYy/93xawZhxalJYmN2aHbjhs6u4zGggo0+typaBqex89Nmo7Vcw==","iterations":10000}}```
Short Authorization Description
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/users/admin>

{"name":"admin","roles":["admin"]}```
Full Authorization Description
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/users/admin?full>

{

"name": "admin",

"roles": [{

"name": "admin",

"permissions": [{

"resourceAction": {

"resource": {

"name": ".*",

"type": "DATASOURCE"

},

"action": "READ"

},

"resourceNamePattern": ".*"

}, {

"resourceAction": {

"resource": {

"name": ".*",

"type": "DATASOURCE"

},

"action": "WRITE"

},

"resourceNamePattern": ".*"

}, {

"resourceAction": {

"resource": {

"name": ".*",

"type": "CONFIG"

},

"action": "READ"

},

"resourceNamePattern": ".*"

}, {

"resourceAction": {

"resource": {

"name": ".*",

"type": "CONFIG"

},

"action": "WRITE"

},

"resourceNamePattern": ".*"

}, {

"resourceAction": {

"resource": {

"name": ".*",

"type": "STATE"

},

"action": "READ"

},

"resourceNamePattern": ".*"

}, {

"resourceAction": {

"resource": {

"name": ".*",

"type": "STATE"

},

"action": "WRITE"

},

"resourceNamePattern": ".*"

}]

}]

}```
Roles
List roles
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/>

["admin","druid_system","dataGrip"]```
Create Role
```curl -XPOST -H'Content-Type: application/json'  -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/dataGrip>
curl -XGET -H'Content-Type: application/json'  -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles>

["admin","druid_system","dataGrip"]```
Assign Permissions to role
```curl -XPOST -H'Content-Type: application/json' -d @role_permissions.json -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/dataGrip/permissions>```
role_permission.json:
```[

{

  "resource": {

    "name": "wiki.*",

    "type": "DATASOURCE"

  },

  "action": "READ"

},

{

  "resource": {

    "name": "wikiticker",

    "type": "DATASOURCE"

  },

  "action": "WRITE"

}

]```
Modify Role Permissions
Get the current Role Permissions
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/dataGrip/permissions>

[{

"resourceAction": {

"resource": {

"name": "wiki.*",

"type": "DATASOURCE"

},

"action": "READ"

},

"resourceNamePattern": "wiki.*"

}, {

"resourceAction": {

"resource": {

"name": "wikiticker",

"type": "DATASOURCE"

},

"action": "WRITE"

},

"resourceNamePattern": "wikiticker"

}]```
Currently, user jonh with dataGrip role would be able to only read / write datasources with names starting with wiki.*. Trying to access datasources with name druidKafka* would fail with the following error:
[00000][-1] Error -1 (00000) : Error while executing SQL "select * from druid.druidKafka1": Remote driver error: RuntimeException: org.apache.druid.server.security.ForbiddenException: Allowed:false, Message: -&gt; ForbiddenException: Allowed:false, Message:
To give permission to datasources by name druidKafka*, role_permissions.json role_permissions.json file needs to be modified.
Copy the above to a file and amend/add permissions. For example, create a file called role_permission.json:
```[

{

  "resource": {

    "name": "druid.*",

    "type": "DATASOURCE"

  },

  "action": "READ"

},

{

  "resource": {

    "name": "druidKafka.*",

    "type": "DATASOURCE"

  },

  "action": "WRITE"

}

]```
NOTE: resourcePatternName is removed from the Json while saving.
Run the following command to change the permissions for dataGrip role using the above json file:
```curl -XPOST -H'Content-Type: application/json' -d @role_permission.json -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/dataGrip>```
Check the role Permissions
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/dataGrip/permissions>

[{"resourceAction":{"resource":{"name":"druid.*","type":"DATASOURCE"},"action":"READ"},"resourceNamePattern":"druid.*"},{"resourceAction":{"resource":{"name":"druidKafka.*","type":"DATASOURCE"},"action":"WRITE"},"resourceNamePattern":"druidKafka.*"}]```
Try to access the tables as user john now.
Accessing druidKafka1:
[00000][-1] Error -1 (00000) : Error while executing SQL "select count(*) from *druidKafka1*": Remote driver error: RuntimeException: org.apache.druid.server.security.ForbiddenException: Allowed:false, Message: -&gt; ForbiddenException: Allowed:false, Message:
Accessing druidKinesis1:
select count(*) from druidKinesis1
7743372
Query successfully completed.
Delete Role
```curl -XDELETE -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/dataGrip>```
List Roles after Deletion:
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/roles/>

["admin","druid_system"]imply@ip-10-2-203-157:~$```
Managing User with Roles
List User Roles
```curl -XGET -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/users/admin>

{"name":"admin","roles":["admin"]}```
Associate user for Authorization
Once a user is created using the commands above, it can be enabled for Authorization.
```curl -XPOST -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/users/john>

{"error":"User [john] already exists."}```
Assign Role to a User
```curl -XPOST -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/users/john/roles/admin>```
Remove Role from a user
```curl -XDELETE -H'Content-Type: application/json' -uadmin:Pass_From_APISection -k <https://localhost:8281/druid-ext/basic-security/authorization/db/basic/users/john/roles/admin>```


What log is this error in? Is this an ingestion from S3? Are you missing the authentication properties in the input source?

it's an `AccessDenied` from S3 so you would need to make sure your server has permissions to access the files you're trying to read

check here for details: <https://druid.apache.org/docs/latest/development/extensions-core/s3.html#s3-authentication-methods>

run a simple batch ingestion and see if you get segments in the bucket

initially, streaming ingestion will not publish segments, it will publish at handoff which occurs normally at the end of the task duration (or at intermediateHandoffPeriod).

I normally test with the wikipedia sample from the web console loader.

Auth failure now. And I see `WebIdentityTokenCredentialsProvider` is acceptable in the provider chain <https://github.com/apache/druid/blob/8296123d895db7d06bc4517db5e767afb7862b83/cloud/aws-common/src/main/java/org/apache/druid/common/aws/AWSCredentialsUtils.java|here>.

AWS_ROLE_ARN, AWS_WEB_IDENTITY_TOKEN_FILE are available in the env.

```2023-03-28T17:55:54,309 WARN [[index_parallel_wikipedia_nhgflfaj_2023-03-28T17:55:31.446Z]-appenderator-merge] org.apache.druid.java.util.common.RetryUtils - Retrying (1 of 4) in 1,008ms.
java.io.IOException: com.amazonaws.services.s3.model.AmazonS3Exception: Access Denied (Service: Amazon S3; Status Code: 403; Error Code: AccessDenied; Request ID: NETB5ZBG7E7FH9Z7; S3 Extended Request ID: iZluZGHneBhidh5UvQYDvTyJL6iznH89s3WPtoZB2BakmnLm5QblX1SuIKEIrjX1f7md18/sTDcyC7Dx3cKgEw==; Proxy: null), S3 Extended Request ID: iZluZGHneBhidh5UvQYDvTyJL6iznH89s3WPtoZB2BakmnLm5QblX1SuIKEIrjX1f7md18/sTDcyC7Dx3cKgEw==
	at org.apache.druid.storage.s3.S3DataSegmentPusher.pushToPath(S3DataSegmentPusher.java:108) ~[?:?]
	at org.apache.druid.storage.s3.S3DataSegmentPusher.push(S3DataSegmentPusher.java:82) ~[?:?]
	at org.apache.druid.segment.realtime.appenderator.AppenderatorImpl.lambda$mergeAndPush$4(AppenderatorImpl.java:956) ~[druid-server-25.0.0.jar:25.0.0]```

If you describe the pod this is running on do you see the AWS related environment variables set?

Did you use the Druid Helm chart to install or some other method? Did you specify the IRSA annotation on the service account for all service types?

yes, pod does have env vars. I am using operator to create this cluster, yes, druid helm charts..
serviceaccount has an annotation
```Annotations:         <http://eks.amazonaws.com/role-arn|eks.amazonaws.com/role-arn>: arn:aws:iam::&lt;&lt;myaccount&gt;&gt;:role/kubernetes_services/&lt;&lt;myrole&gt;&gt;```

Does the role have access to read from the S3 bucket on both the bucket and the role?

Well read and write most likely is required.

yes, I am able to read/write from a test pod, but not druid

wondering if there is a specific rbac needed for the pods

have you set up the druid service account in druid-operator?eg. serviceAccount: druid-application-sa, you need to add S3 permissions to the service account.

never mind, just enabled trace logging on `com.amazonaws` and noticed that the secret file is not being picked up. This is our k8s setup issue where token file secret is not mounted to druid pods.

```2023-03-28T21:57:58,829 DEBUG [[index_parallel_wikipedia_acdefclb_2023-03-28T21:57:40.714Z]-appenderator-merge] com.amazonaws.auth.AWSCredentialsProviderChain - Unable to load credentials from WebIdentityTokenCredentialsProvider: Unable to locate specified web identity token file: /var/run/secrets/eks.amazonaws.com/serviceaccount/token```

Why would someone want to compact unused segments?  (I suspect that's the reason.)

Check out <https://github.com/apache/druid/pull/10920>

thanks! I also found <https://github.com/deep-bi/spark-druid-segment-reader|this> in the thread

FYI: <https://github.com/deep-bi/spark-druid-segment-reader/issues/1#issuecomment-1379804007>

image.png

FYI2:
Limitation on *deep-bi/spark-druid-segment-reader:*

Personally, I think <https://github.com/apache/druid/pull/10920> is more complete and would be something we look into merging into Apache Druid core extension and officially supported by Druid

It's very expensive :slightly_smiling_face:

Pro is $7.25/user/mo, we have 1430 users right now so that'd be ~$10,000/mo

I wonder if ASF and Trino are paying, or if it's donated somehow.

maybe they get a special deal/discount for being OSS?

otherwise, that is still a lot for donation (like 1M a year haha)

You should be able to search old messages here

i recommend pinning this so more people know about it

I thought I already pin it. Thanks :sweat_smile:

The one that says “Slack Chat History”

although the searching capability is not as nice as Slack

you will have to mount the file on the container.

In the same location, where common runtimer properties is stored in the container

do you see the kill tasks have run in the console?  If kill has run and the segments were marked as unused, they should be gone

I checked the S3 bucket as well, there were no segments which have been marked as unused, So I think it worked? :slightly_smiling_face:

also, this sys.segments is a view
for the seeing all segments you need to query directly on (pg|mysql) druid_segments table

It looks like your replicants are set to 1

what is hot tier and default tier actually? Are these my replicants? To fix this, should I set hot as 2 in loadForever rule?

Those would be your tiers of historicals and you are placing one in each tier but they are not replicas.  A replica would be replication within the same tier.

<https://druid.apache.org/docs/latest/configuration/index.html|Docs> say druid.coordinator.kill.on is false (off) by default.  (I didn't know that.)  I'm curious, which video said this?

Auto-kill was enabled by default in 0.23.0, and after some feedback, we decided it was better to have auto-kill disabled by default in 24.0.0

<https://github.com/apache/druid/pull/12187#issuecomment-1163536212> - This comment and thread talks about the risk in enabling auto-kill.

Hey,
I am using python to pull data out of a druid table and push it in kafka which is connected to that druid table and has live data flowing through it.
So will my Kafka data become redundant after pulling the data from the druid .?

my kafka stores data for 10 days lets say

JVM metrics should be emitted for individual peon processes, they can be identified by their host:port

we have prometheus based emitter where 9102 port is used

Do we need to open these ports as well for scraping these metrics

with Prometheus - you will likely need to set up a push-gateway to collect peon metrics

<https://druid.apache.org/docs/latest/development/extensions-contrib/prometheus.html#override-properties-for-peon-tasks>

The push gateway for peon metrics doesn't scale that well  in my experience, and there are issues with push metrics being used in this manner (a push gateway never deletes metrics and we've found old metrics still being *emitted* long after the task has stopped).

Maybe it was the scale of peons we had running that caused the push gateway to stop responding though so you might be OK :crossed_fingers:

Hi Druid Experts
I have promethues metrics enabled with `exporter` strategy for peon metrics I have set the following in my helm chart

```druid_indexer_fork_property_druid_emitter_prometheus_strategy: "pushgateway"
    druid_indexer_fork_property_druid_emitter_prometheus_pushGatewayAddress: "<http://pushgateway-address>"```
how to check for the peon metrics are emitted or not
when i submit a task initial log i am seeing `druid_indexer_fork_property_druid_emitter_prometheus_pushGatewayAddress` getting set however this is not getting set in logs `druid_indexer_fork_property_druid_emitter_prometheus_strategy`

kindly help. Thanks in advance.

Are you setting  `druid_indexer_fork_property_druid_emitter_prometheus_strategy` = `pushgateway` ?

yes i am setting `druid_indexer_fork_property_druid_emitter_prometheus_strategy` in helm chart but in runtime.properties file generated during k8s start up its not present.

`druid_indexer_fork_property_druid_emitter_prometheus_pushGatewayAddress`  this is present  in runtime.properties file

ok... so that seems like it is missing something in the helm chart templates. I'll take a look. Can you share the middle manager config in your values.yaml?

The docker image uses the `druid.sh &lt;Service Name&gt;` as the entrypoint for the image, It reads the `env` at pod start up and finds all the `druid_*` environment variables, converts `_` inro `.` and writes them into a tmp conf folder that it uses to initiate the service. I tested it with a docker compose with the variable set in the environment file (the equivalent of the `config:` section of the helm chart) and I did see the property in the config file. The location of the config file depends on the startup environment as well. It depends on whether `DRUID_SINGLE_NODE_CONF`  is defined in the environment. In my case the file was located at: `/tmp/conf/druid/single-server/micro-quickstart/middleManager`

<@U030K4UM3H7> Apologies for responding late and Thanks for checking on this !!!

We will verify one more time from our side and then get back to you

GROUP BY has different limitation depending on how it's done.  See this <https://druid.apache.org/docs/latest/querying/sql.html#union-all|doc section>.  I suggest playing with it a bit to understand better.  For a "taable-level" query you can use column aliasing, but you can't GROUP BY.  For "table level" you can GROUP BY, but you can't use column aliasing.

Hey <@U0411DE2SS0>, thanks for this doc, I was wondering it there is a workaround for these limitations in druid without using some other query engine?

I tried to mock this up with datasets I had handy.  You might be able to using common table expressions (CTEs).  Eg, for my datasets:
```WITH table1 (myTime, event, myCount) AS 
(select __time, cityName, count(*) FILTER (WHERE cityName = 'Aachen') from "wikipedia" 
where cityName = 'Aachen' 
group by 1,2
--having cityName = 'Aachen'
)
,
table2 (myTime, event, myCount) AS (select 'allTime', d3_email, count(*) from dataTest group by 1,2)
select * from table1
union all 
select * from table2```

(I didn't seem to need the HAVING clause.)

But I think you probably need that dummy value for the missing column in the one query.

count distinct by default will get you approximate count. So there will some variation in results. For the sql query I would use approx_count_distinct_ds_theta ....this will give exact count for values less than the sketch size (default of 32k).

Do you recommend ds_theta over ds_hll for this?

Hello, let me to explain you.
Sql query give correct results because it select all data (regardless of segmentation) and find the distinct customer id / version.
Pydruid query not give correct results because it finds distinct customer_id / version / segment(month)
so if a version exists in 2 months, even if it is the same customer, it will bring me these records as well.
for example:
sql returns:
Version	Number_of_Unique_Borrowers
60	108028
61	107814
62	44475
but Pydruid returns:
Version  Number of Unique Borrowers
     60                      122353
     61                      119460
     62                       44475

this is due to the approximate count distinct

hmm ok, so how pydruid query should be to give the same results with sql?

<@U04P4A4J3JP> the main difference between hll and theta is that theta will give you exact counts upto the sketch size. this is useful in many scenarios. HLL does have a lower memory fottprint however

<@U04QLJ4A864> you will have to turn off approx count distinct but that will affect performance a lot.

I don't know if pydruid can handle theta sketch (I think it generates native query directly). Even if you use theta sketch it will still be approximate beyond the size of the sketch

theta sketches are supported in pydruid <https://github.com/druid-io/pydruid>

here is a pydruid with theta sketch
```q = query.groupby(
    datasource='wikipedia_rollup',
    granularity='all',
    intervals='1970-01-01/2023-01-01',
    aggregations={'distinct_count':thetasketch('channel', size=262144)
           
    }
    
)```

in the sql use approx_count_distinct_ds_theta(channel,262144). this will give you exact count upto 262144 and approx after that

hmm theta gives approximate result. if I want to get the exact number?

The highest you can get an exact count depends on how much data you can store in memory. All you can do is set an upper boundary.

Check this Druid tutorial: <https://druid.apache.org/docs/latest/tutorials/tutorial-msq-extern.html> It shows an example of loading <https://druid.apache.org/data/wikipedia.json.gz>.

You can also check the web console’s load data panel to show you a variety of sources that are built-in: <https://druid.apache.org/docs/latest/tutorials/index.html#load-data>

I tested my URI from my server where the data is and I got refused connection even I provided the right username and the password
I think I have to configure something else to let my data accessible ?

you need to make sure that the URI where your data is hosted i accessible from the servers running Druid. Can you ssh into on of the Druid servers and try accessing your data from that server via cURL? The issue is likely not in Druid's configuration but in the network settup

You can use the sys.segments table where you can see segment per segement, datasource per datasources some usefull info

<https://druid.apache.org/docs/latest/querying/sql-metadata-tables.html>

Screenshot 2023-03-30 at 9.04.12 AM.png

Pro tip: If you use the web console, go to the datasources page and click the `...` button. There is an option where it will open the query view with the sql query it used to generate the view for the datasource view.

This is a good starting point if you want to tweak the query to show something different from the sys.segments table

I think the easier is to use the SQL api to import using append mode <https://docs.imply.io/latest/druid/multi-stage-query/reference/#insert>
if you are sure that all data is, in fact, a backfill, then you can do then in parallel

Appending would not duplicate data if backfill overlaps realtime for some time?

yes, it will! you can discard (at least on kafka) all record prior to a epoch[ at the real-time ingestion], and add a where condition on the select for the append [batch backfill]

whats the difference from sql api and index_parallel?

the sql ingestion is a new (somewhat easier) way to import data from multiple sources, but nothing prevents it from running just from one s3
it has a nice UI for the progress, but in the background, it's managing and issuing index_parallel tasks

I was checking, the earlyMessageRejectionPeriod is available at all ingestion, so kinesis can use it

the issue is that it's a period, not a fixed date time :thinking_face:

so if the realtime has a daily query granularity and monthly segment granularity, the rejection period might be tricky

oh, you can also include a filter in the kinesis ingestion, let the where in the definition until the overload is synced (I don't know how it works, it like kafka, with offsets?)

I would need to check the filter in the kinesis ingestion

is there examples for the sql ingestion for s3 somewhere? it’s pretty new to me

it is available in json and parquet, I have found parquet to be better in performance for ingesting

<https://druid.apache.org/docs/latest/multi-stage-query/examples.html#select-with-extern-and-join>

I think it only supports json / csv for now

<https://druid.apache.org/docs/latest/ingestion/data-formats.html#formatting-data> csv, json or tsv

oh, nice ! Besides text formats, Druid also supports binary formats such as <https://druid.apache.org/docs/latest/ingestion/data-formats.html#orc|Orc> and <https://druid.apache.org/docs/latest/ingestion/data-formats.html#parquet|Parquet> formats.

seems like mine would fall into Insert with Rollup section but with an interval I guess

you want to rollup? eg: group all records within some some period into just (hopefully) one row ?

after the ingestion, you can activate the auto-compation to make sure that all rows are as compacted as it can be

yeah my current index_parallel spec might be a bit complex to translate into sql since I also have some transform specs, but for the granularity spec:
```"granularitySpec": {
                "type": "uniform",
                "segmentGranularity": "MONTH",
                "queryGranularity": "DAY",
                "rollup": true,
                "intervals": [
                    "2023-02-15T00:00:00.000Z/2023-02-22T00:00:00.000Z"
                ]
            },```

if is a lot of data, and do not use rollup, maybe you be interested in the 'Range' compaction, it's a 2 step compaction, but it's the best of all worlds

&gt; <https://imply.io/blog/multi-dimensional-range-partitioning/> &lt; more on the range partitioning

Also new to me but I think given we use rollup that might no apply

it only makes sense if your segments are so big that in just one segmentGranularity you have more than one segment file

but also in the realtime ingestion we have for that particular datasource, we also have rollup and same segmentGranularity and queryGranularity

The startTime and endTime need to be start and end of some months, eg '2023-01-01' and '2023-02-28'.  It sounds like they're not?

Otherwise it's stopping you because bad things will happen with existing data.

Even i tried with just dates its still same

The end time needs to be 12 o'clock midnight of the following day, because it's an excluded endpoint, e.g. `2020-02-01:00:00:00 &lt;= time &lt; 2020-03-01:00:00:00`. (my syntax not exact)

Thanks <@U04C4593VDY> I am passing right window for the interval. However i would like to load the data into MONTH partitions where it is complaining that it not aligned with interval values. If i give `PARTITIONED BY HOUR` it works fine. I do not want to load this data with too many segments by hour

For Example :
```REPLACE INTO "${target} OVERWRITE
WHERE __time &gt;= TIMESTAMP '2022-12-01 00:00:00' AND __time &lt; TIMESTAMP '2023-03-30 00:00:00'
SELECT * from "${source}" WHERE __time &gt;= TIMESTAMP '2022-12-01 00:00:00' AND __time &lt; TIMESTAMP '2023-03-30 00:00:00'
PARTITIONED BY MONTH```

This works for me (on one of my test datasets) --

```REPLACE INTO test_mo OVERWRITE
WHERE __time &gt;= TIMESTAMP '2022-12-01 00:00:00' AND __time &lt; TIMESTAMP '2023-07-01 00:00:00'
SELECT * from test_mo WHERE __time &gt;= TIMESTAMP '2022-12-01 00:00:00' AND __time &lt; TIMESTAMP '2023-07-30 00:00:00'
PARTITIONED BY MONTH```
The OVERWRITE clause should have both dates be on the 1st of the month ... in the SELECT clause you can see my time range was shorter, but it still worked.  Your example about had the OVERWRITE end date on 3/30, which is not correct, it should be 4/1.

If you use 4/1 and it still doesn't work, then the only other thing I can think of that could be an issue is timezone?  Did you change default timezone on your cluster?  I don't know if timezone actually matters here or if all is assumed UTC, but it is a thought of what to check next.

Thanks <@U04C4593VDY> for getting back to me. Let me try with that and see what happens. Appreciate your help !!

Screenshot 2023-03-30 at 6.05.42 PM.png

Hi Tilak,

`= null` should always return false .... you cannot equate NULL to anything.

The appropriate expression to use is `is null` ... please check to see if that works better.

Screenshot 2023-03-30 at 6.23.53 PM.png

Not sure for the second part, I see what you're saying.  For the first query, does `IS NULL` return something different from `= NULL`?   `= NULL` should always be false, NULL doesn't = anything, not even NULL.

try is null and is not null instead of = null
```select JSON_VALUE(event,'$.layer') from "kttm-nested-v2-2019-08-25" where JSON_VALUE(event,'$.layer') is not null```

there is also a bug with null value filtering that can occur if you ingest a value of `"null"`  (not `null`, but the string) described by <https://github.com/apache/druid/pull/13714>, and i’ve recently discovered another bug with null value filtering when the nested field is mixed types and the value is being coerced to a numeric type (e.g. `JSON_VALUE(nested, '$.x' RETURNING BIGINT)`)

but yeah, need to use `is null`/`is not null`

and it should work (hopefully, except for in the cases of the bugs i described)

the code freeze for druid 26 should be happening pretty soon, so will try to get the other known bug fixed before release

I found this via compaction logs. Simply search for LocalDataSegmentPuller.

Bummer! Compaction process itself is already inefficient by downloading all the data before hand first.

did you actually write a JSON based join query or do you have a SQL query that you are are posting the explain plan for?

I actually wrote native query <@U030W50CNAC>

oh how! I have never written a native query with a join. I didn't know anyone did. I mean I know they are possible but I just assumed everyone uses SQL for stuff beyond a simple group by

Are you able to reproduce this on something like the example wikipedia dataset?

<@U030W50CNAC> - I’ve not tried doing that. Let me give it a try..

no, in Druid everything lives in one database is the `druid` schema. You would need to prefix your datasource names to make a hierarchy

Have you tried `druid.s3.forceGlobalBucketAccessEnabled` from <https://druid.apache.org/docs/latest/development/extensions-core/s3.html#connecting-to-s3-configuration>

there’s also
&gt; Add`-Daws.region=us-east-1` to the `jvm.config` file for all Druid services

<@U04RDJ4707Q> did it solve your problem?