[destination-mysql] - Table Creation Control and Data Type Mapping from Redshift to MySQL #38031
Labels
area/connectors
Connector related issues
autoteam
community
connectors/destination/mysql
team/use
type/bug
Something isn't working
Connector Name
destination-mysql
Connector Version
1.0.0
What step the error happened?
During the sync
Relevant information
Issues:
Can Airbyte allow users to create destination tables in MySQL and sync data, rather than having the Airbyte MySQL connector manage table structure creation or updates?
When transferring data from Redshift to MySQL via Airbyte, how can we ensure that a datetime field in Redshift is mapped correctly as a datetime field in MySQL? Additionally, how are other data types in Redshift mapped to relevant data types in MySQL during this data transfer process?
Can anyone help with resolving these issues or point me to any existing functionality that addresses them? Below are the table structures.
Relevant Table Structures
Redshift Table Structure:
CREATE TABLE reports.sales_order_report (
salesteammemberid character varying(36) ENCODE lzo,
sellerworkspaceid character varying(36) ENCODE lzo,
state character varying(255) ENCODE lzo,
orderdate timestamp without time zone ENCODE az64,
nooforders integer ENCODE az64,
totalvalue numeric(18, 0) ENCODE az64,
updated_at timestamp without time zone ENCODE az64,
created_at timestamp without time zone ENCODE az64
) DISTSTYLE AUTO;
Airbyte Destination MySQL Table Structure
CREATE TABLE
sales_order_report
(state
text,orderdate
text,created_at
text,nooforders
bigint DEFAULT NULL,totalvalue
decimal(38,9) DEFAULT NULL,updated_at
text,salesteammemberid
text,sellerworkspaceid
text,_airbyte_raw_id
varchar(36) NOT NULL,_airbyte_extracted_at
timestamp(6) NOT NULL,_airbyte_meta
json NOT NULL,KEY
dedup_idx
(sellerworkspaceid
(50),_airbyte_extracted_at
,salesteammemberid
(50),state
(50),orderdate
(50),updated_at
(50)),KEY
extracted_at_idx
(_airbyte_extracted_at
),KEY
raw_id_idx
(_airbyte_raw_id
)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Required Destination MySQL Structure: i require destination to be of below format
CREATE TABLE
sales_order_report
(state
varchar(255) DEFAULT NULL,orderdate
text,created_at
text,nooforders
bigint DEFAULT NULL,totalvalue
float(18,0) DEFAULT NULL,updated_at
text,salesteammemberid
varchar(36) DEFAULT NULL,sellerworkspaceid
varchar(36) DEFAULT NULL,_airbyte_raw_id
varchar(36) NOT NULL,_airbyte_extracted_at
timestamp(6) NOT NULL,_airbyte_meta
json NOT NULL,KEY
dedup_idx
(sellerworkspaceid
(36),_airbyte_extracted_at
,salesteammemberid
(36),state
(255),orderdate
,updated_at
),KEY
extracted_at_idx
(_airbyte_extracted_at
),KEY
raw_id_idx
(_airbyte_raw_id
)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Contribute
The text was updated successfully, but these errors were encountered: