https://linen.dev logo
Join Slack
Powered by
Hi all. I'm trying to understand the airbyte UI (0...
# replication-troubleshooting
d
Hi all. I'm trying to understand the airbyte UI (0.40.18) and troubleshoot a sync. When my job completes I get a '`Sync Succeeded 10,000,000 emitted records | 10,000,000 committed records`' message. But when I do a count in the raw table I only have 9,986,326 rows. I found a line in the log which says 
A total of 13674 record(s) of data from stream AirbyteStreamNameNamespacePair{name='events_196', namespace='analytics_raw'} were invalid and were ignored.
My sync is a raw sync with no normalisation postgres (RDS) to Redshift Serverless (using destination-redshift 0.3.51) going direct (not via S3) How do I figure out why those rows are invalid as all rows are required? (This was a test copy of 10M rows from a 1.7B row db) Why does the UI say its committed 10,000,000 records when it hasn't?
n
Hi Dave, could you please include your full logs?
d
Hi Nataly, I have this one, are there others?
c8413435_d0cc_4704_bb90_7307ee61c71b_logs_49_txt.txt
Hi, Since the above I've extracted the raw data I had and then run
Copy code
select pId+1 as s_missing, Id-1 as e_missing, e_missing - s_missing as gap
from (
		select 
			Id, 
			lag(Id) over (order by Id) pId 
		from analytics_raw.events_196) e
where pId <> (Id-1)
order by gap desc
Which gives me the Ids that are missing. I've then started looking in the source to try and spot something obviously wrong with those records but I haven't spotted anything yet.
@Nataly Merezhuk (Airbyte) I've got the list of failed rows then I've built insert statements for those rows to insert them into the _arbyte table. currently only tried 2 but bth got [2022-11-16 160739] [XX000] ERROR: Invalid input [2022-11-16 160739] Detail: [2022-11-16 160739] ----------------------------------------------- [2022-11-16 160739] error: Invalid input [2022-11-16 160739] code: 8001 [2022-11-16 160739] context: Value is too large for SUPER string type: 65535 [2022-11-16 160739] query: 0 [2022-11-16 160739] location: partiql_postgres.cpp:26 [2022-11-16 160739] process: padbmaster [pid=22029] [2022-11-16 160739] ----------------------------------------------- when inserting into Redshift Question is, is there a way to avoid this? It looks like Postgres max json size is 256MB and the Redshift max super size is 1MB (according to the AWS docs but 65535 characters according to the error)
n
Thanks so much for all the details! Could you please create a GitHub issue for this?
d
Raised as https://github.com/airbytehq/airbyte/issues/19990 But looks like my root cause might be fixed in a Redshift update when this goes GA https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-sql-capabilities-speed-data-warehouse-migrations-preview/ But still raised it as the UI reporting Succeeded could have caused trouble 😀 (good job I check the actual data not rely on the UI)
u
Thank you so much!
m
@Dave Tomkinson Did you ever resolve the issue? Any idea when that update will go GA? I’m seeing something similar
d
Hi, unfortunately I have no idea when it's going to go GA 😞 we're still waiting for that too; for us it was acceptable to lose some rows so we went with that for now. We've still got a couple of tables we're holding back on that we do need the wider column for. The Auto Copy feature which went into preview around the same time seems to have a preview end date of Feb 28th -> https://docs.aws.amazon.com/redshift/latest/dg/loading-data-copy-job.html so I'm hoping it'll go GA in this quarter... 🤞
m
Thanks for the response. Sorry if I missed it— did you find a workaround in this case?
d
Also we switched off the airbyte normalisation to get the raw data to load then we do that step in dbt
m
So the raw rows load without normalization?
d
I think, it was was a while ago now, that if we loaded with normalisation enabled it failed but if we loaded in raw mode (no normalisation) then we got the rows through which were below the limit as our row size varied. we only had a loss of about 0.5% with rows that were too big, so we accepted the loss. If your rows are all over then sorry, no work around. If all your rows are over then (if possible) I guess you could push through kinesis firehose and use a lambda to fix the data on the way through...? but that's not an airbyte fix.
m
Sounds good. Thanks Dave! Honestly this is more on Redshift, since a 1MB limit is pretty small -_-
d
no worries, let me know if you find anything i've missed!
@Matt Palmer just spotted in the docs that SUPER is 16MB! https://docs.aws.amazon.com/redshift/latest/dg/r_SUPER_type.html However when I tried to do a direct insert into Redshift Serverless I got 
JSON_PARSE() error: String value exceeds the max size of 65535 bytes:
and 
Value is too large for SUPER string type: 65535
Might work in a cluster? or the docs might be talking about preview... but not stating that. Not sure if Airbyte does a check before insert so may need to wait for an Airbyte destination update anyway (once it really works)
been pointed at this https://docs.aws.amazon.com/redshift/latest/dg/limitations-super.html which points out that it is still in preview. But I also spotted this (in preview docs) • An individual value within a SUPER object is limited to the maximum length of the corresponding Amazon Redshift type. For example, a single string value loaded to SUPER is limited to the maximum VARCHAR length of 65535 bytes. Which might still cause a problem with the Airbyte load as Airbyte mushes everything into one column, so if you're transferring a table that has a large JSON column that'll be combined into a single object by Airbyte... I think... ?
m
@Dave Tomkinson Thanks— so I dont think this is actually my issue. I believe the root cause is that I have a 
GEOMETRY
column that’s being converted to a string. The max 
VARCHAR
length isn’t sufficient, so the load is failing. Airbyte will need to add support for 
GEOMETRY
read/writes/
4 Views
Open in Slack
PreviousNext
Powered by