https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
p
If you were ingesting into the same table again, what was your 
appendToExisting
setting? https://druid.apache.org/docs/latest/ingestion/native-batch.html#ioconfig
d
I haven't set appendToExisting in my ingestion
p
Another check also, what does a simple 
COUNT
give you as the row count? You could 
GROUP BY
a time period, too, so that you can see if it’s the entire ingestion, or just one part of it
On another note, if you’re going to filter by 
facilitytype_id
you may want that to be a string so that you get a secondary index on it – much better for filtering 🙂
🙌 1
d
Thanks. When i do the count(*) i see that it is double the original source count.
Just thinking whether i need to set dropExisting=true and appendToExisting=false in my ingestion?
p
Quite possible!
There’s a comprehensive bit in the docs - but this may just be learning for later 😄
I would also go back to the tasks to see what the row counts were when you ingested – it will tell you what actually got `INSERT`ed.
d
I just tried adding the below config in ingestion and still the duplicate issue is there
"ioConfig": { "appendToExisting": false, "dropExisting": true }
s
can you describe the steps you took to ingest the source data? appendToExisting and dropExisting will only affect the most recent ingestion and the time intervals that it includes. Is you have prior data ingested with appendToExisting true, that might have caused the duplicates, then you will need to reingest that timeframe in order for the dropExisting to affect the corresponding time interval. If you are on version 24+ a 
REPLACE INTO table OVERWRITE WHERE <__time condition> SELECT ... FROM <source>...
will do this.
d
Hi @Sergio Ferragut i have removed manually the segments from S3 and also from Druid console for this particular table and re-ingested and even then the table data is duplicated. Also i had never ingested data with appendToExisting.
Please find below the query used for ingestion
REPLACE INTO fact_extrapolated_revenue OVERWRITE ALL WITH fact_extrapolated_revenue_table AS (SELECT * FROM TABLE( EXTERN( '{\"type\":\"s3\",\"prefixes\":[\"s3://data-dev/druid_poc_1/fact_extrapolated_revenue\"]}', '{\"type\":\"csv\",\"findColumnsFromHeader\":true}', '[{\"name\":\"product_id\",\"type\":\"string\"}, {\"name\":\"facilitytype_id\",\"type\":\"long\"}, {\"name\":\"month_end_datekey\",\"type\":\"string\"} ,{\"name\":\"dist_total_units\",\"type\":\"long\"}, {\"name\":\"dist_total_revenue\",\"type\":\"long\"}, {\"name\":\"modified\",\"type\":\"string\"}]' ) )) SELECT coalesce(TIME_PARSE(fez.month_end_datekey,'yyyyMMdd'),current_timestamp) AS __time, EXTRACT(MONTH from coalesce(TIME_PARSE(fez.month_end_datekey,'yyyyMMdd'),current_timestamp)) AS sale_month, EXTRACT(YEAR from coalesce(TIME_PARSE(fez.month_end_datekey,'yyyyMMdd'),current_timestamp)) AS sale_year, EXTRACT(QUARTER from coalesce(TIME_PARSE(fez.month_end_datekey,'yyyyMMdd'),current_timestamp)) AS sale_quarter, fez.product_id, fez.facilitytype_id, fez.dist_total_units, fez.dist_total_revenue FROM fact_extrapolated_revenue_table fez PARTITIONED BY YEAR
i have also confirmed the source to ensure that there is no duplicates in source
Also please find below the sample record repeated.
s
Got it. You’ve queried the csv files on S3 to verify that? Just making sure it isn’t a data extraction issue. If not, then this does seem like a bug. I can try to reproduce. How many csv files do you have? How large are they?
d
Hi @Sergio Ferragut i have queried the S3 file to verify that. Please find below the screenshot of queried file from S3
I am not choosing all columns from source file while ingesting and only selected columns.
there are total 2000 objects and total size is 1.6 GB
each file is approax 823 KB
s
A few other thoughts: Is the count of rows loaded to Druid different than the count of lines (minus headers) in the csvs? If the lines in the file are unique, What’s the key? Is the full key selected for ingestion?
d
Hi @Sergio Ferragut i didn't get what exactly you meant by key. We are selecting all the rows for loading. But a few columns are omitted when ingesting to druid.
Also we verified in the source and couldn't identify any duplicate records
s
I mean what makes the rows in the files unique. You mentioned that you were not selecting all the columns so I was wondering whether there was another column in the file that makes those rows unique. Also, a simple test of this is to count the rows and count the lines in the files to see if ir is the same count.
d
actually the count in the druid is way more than the rows in the file
another thing to note is that, we have tried ingesting the file in parquet format now we get the correct results
So CSV format is giving this inconsistent data
s
thanks. I will try to reproduce.
👍 1
d
Hi @Sergio Ferragut another thing to note is that i have below folder structure in my S3
So when i ingest by mentioning the S3 path as s3://data-dev/druid_poc_1/fact_extrapolated_revenue, will it take all folders? i mean will it by default take s3://data-dev/druid_poc_1/fact_extrapolated_revenue* ?
s
That's quite the gotcha! Glad you figured it out!
After speaking with @Vijay Narayanan, he mentioned that the S3 API that matches the prefix, will match all four of those folders because they all match the prefix. A workaround is to rename the 
fact_extrapolated_revenue
folder to 
fact_extrapolated_revenue_csv
so it becomes unique.
d
thank you very much
Open in Slack
PreviousNext
Powered by