· 6 years ago · Aug 12, 2019, 12:40 PM
1"objects": [
2 {
3 "output": {
4 "ref": "DestinationRDSTable"
5 },
6 "input": {
7 "ref": "S3InputDataLocation"
8 },
9 "dependsOn": {
10 "ref": "RdsMySqlTableCreateActivity"
11 },
12 "name": "DataLoadActivity",
13 "id": "DataLoadActivity",
14 "runsOn": {
15 "ref": "Ec2Instance"
16 },
17 "type": "CopyActivity"
18 },
19 {
20 "*password": "#{*myRDSPassword}",
21 "name": "rds_mysql",
22 "jdbcProperties": "allowMultiQueries=true",
23 "id": "rds_mysql",
24 "type": "RdsDatabase",
25 "rdsInstanceId": "#{myRDSInstanceId}",
26 "username": "#{myRDSUsername}"
27 },
28 {
29 "instanceType": "t1.micro",
30 "name": "Ec2Instance",
31 "actionOnTaskFailure": "terminate",
32 "securityGroups": "#{myEc2RdsSecurityGrps}",
33 "id": "Ec2Instance",
34 "type": "Ec2Resource",
35 "terminateAfter": "2 Hours"
36 },
37 {
38 "database": {
39 "ref": "rds_mysql"
40 },
41 "name": "RdsMySqlTableCreateActivity",
42 "runsOn": {
43 "ref": "Ec2Instance"
44 },
45 "id": "RdsMySqlTableCreateActivity",
46 "type": "SqlActivity",
47 "script": "#{myRDSCreateTableSql}"
48 },
49 {
50 "failureAndRerunMode": "CASCADE",
51 "resourceRole": "DataPipelineDefaultResourceRole",
52 "role": "DataPipelineDefaultRole",
53 "pipelineLogUri": "s3://envoy-pipeline-logs/",
54 "scheduleType": "ONDEMAND",
55 "name": "Default",
56 "id": "Default"
57 },
58 {
59 "name": "DataFormat1",
60 "id": "DataFormat1",
61 "type": "CSV"
62 },
63 {
64 "database": {
65 "ref": "rds_mysql"
66 },
67 "name": "DestinationRDSTable",
68 "insertQuery": "#{myRDSTableInsertSql}",
69 "id": "DestinationRDSTable",
70 "type": "SqlDataNode",
71 "table": "#{myRDSTableName}",
72 "selectQuery": "select * from #{table}"
73 },
74 {
75 "directoryPath": "#{myInputS3Loc}",
76 "dataFormat": {
77 "ref": "DataFormat1"
78 },
79 "name": "S3InputDataLocation",
80 "id": "S3InputDataLocation",
81 "type": "S3DataNode"
82 }
83 ],
84 "parameters": [
85 {
86 "description": "RDS MySQL password",
87 "id": "*myRDSPassword",
88 "type": "String"
89 },
90 {
91 "watermark": "security group name",
92 "helpText": "The names of one or more EC2 security groups that have access to the RDS MySQL cluster.",
93 "description": "RDS MySQL security group(s)",
94 "isArray": "true",
95 "optional": "true",
96 "id": "myEc2RdsSecurityGrps",
97 "type": "String"
98 },
99 {
100 "description": "RDS MySQL username",
101 "id": "myRDSUsername",
102 "type": "String"
103 },
104 {
105 "description": "Input S3 file path",
106 "id": "myInputS3Loc",
107 "type": "AWS::S3::ObjectKey"
108 },
109 {
110 "helpText": "The SQL statement to insert data into the RDS MySQL table.",
111 "watermark": "INSERT INTO #{table} (col1, col2, col3) VALUES(?, ?, ?) ;",
112 "description": "Insert SQL query",
113 "id": "myRDSTableInsertSql",
114 "type": "String"
115 },
116 {
117 "helpText": "The name of an existing table or a new table that will be created based on the create table SQL query parameter below.",
118 "description": "RDS MySQL table name",
119 "id": "myRDSTableName",
120 "type": "String"
121 },
122 {
123 "watermark": "CREATE TABLE pet IF NOT EXISTS (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), gender CHAR(1), birth DATE, death DATE);",
124 "helpText": "The idempotent SQL statement to create the RDS MySQL table if it does not already exist.",
125 "description": "Create table SQL query",
126 "optional": "true",
127 "id": "myRDSCreateTableSql",
128 "type": "String"
129 },
130 {
131 "watermark": "DB Instance",
132 "description": "RDS Instance ID",
133 "id": "myRDSInstanceId",
134 "type": "String"
135 }
136 ],
137 "values": {
138 "myRDSInstanceId": "{rds_instance}",
139 "myRDSUsername": "master",
140 "myRDSTableInsertSql": "INSERT INTO #{table}(transaction_id,subscription_id,transaction_type,transaction_status,escrow_status,created_at,merchant_account,currency_iso_code,service_fee,tax_amount,tax_exempt,purchase_order_number,order_id,refund_transaction_id,payment_instrument_type,credit_card_type,cardholder_name,last_four_of_credit_card,expiration_date,credit_card_customer_location,payment_method_token,credit_card_unique_identifier,processor_response_code,processor_response_text,gateway_rejection_reason,avs_postal_code_response_code,avs_street_address_response_code,cvv_response_code,country_of_issuance,issuing_bank,durbin_regulated,commercial,prepaid,payroll,healthcare,debit,type,transaction_source,amount_authorized,settlement_amount,settlement_date,amount_submitted_for_settlement) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
141 "*myRDSPassword": "{pwd}",
142 "myEc2RdsSecurityGrps": "{sec_grp}",
143 "myRDSCreateTableSql": "CREATE TABLE IF NOT EXISTS braintree (transaction_id VARCHAR(255),subscription_id VARCHAR(255),transaction_type VARCHAR(255),transaction_status VARCHAR(255),escrow_status VARCHAR(255),created_at datetime,merchant_account VARCHAR(255),currency_iso_code VARCHAR(255),service_fee float,tax_amount float,tax_exempt bit,purchase_order_number bigint,order_id bigint,refund_transaction_id VARCHAR(255),payment_instrument_type VARCHAR(255),credit_card_type VARCHAR(255),cardholder_name VARCHAR(255),last_four_of_credit_card int,expiration_date date,credit_card_customer_location VARCHAR(255),payment_method_token VARCHAR(255),credit_card_unique_identifier VARCHAR(255),processor_response_code VARCHAR(255),processor_response_text VARCHAR(255),gateway_rejection_reason VARCHAR(255),avs_postal_code_response_code VARCHAR(255),avs_street_address_response_code VARCHAR(255),cvv_response_code VARCHAR(255),country_of_issuance VARCHAR(255),issuing_bank VARCHAR(255),durbin_regulated VARCHAR(255),commercial VARCHAR(255),prepaid VARCHAR(255),payroll VARCHAR(255),healthcare VARCHAR(255),debit VARCHAR(255),type VARCHAR(255),transaction_source VARCHAR(255),amount_authorized float,settlement_amount float,settlement_date datetime,amount_submitted_for_settlement float,PRIMARY KEY (transaction_id));",
144 "myInputS3Loc": "{s3_loc}",
145 "myRDSTableName": "{table_name}"
146 }
147}
148
149amazonaws.datapipeline.taskrunner.TaskExecutionException: Error copying record at amazonaws.datapipeline.activity.copy.SingleThreadedCopyActivity.processAll(SingleThreadedCopyActivity.java:65) at amazonaws.datapipeline.activity.copy.SingleThreadedCopyActivity.runActivity(SingleThreadedCopyActivity.java:35) at amazonaws.datapipeline.activity.CopyActivity.runActivity(CopyActivity.java:22) at amazonaws.datapipeline.objects.AbstractActivity.run(AbstractActivity.java:16) at amazonaws.datapipeline.taskrunner.TaskPoller.executeRemoteRunner(TaskPoller.java:136) at amazonaws.datapipeline.taskrunner.TaskPoller.executeTask(TaskPoller.java:105) at amazonaws.datapipeline.taskrunner.TaskPoller$1.run(TaskPoller.java:81) at private.com.amazonaws.services.datapipeline.poller.PollWorker.executeWork(PollWorker.java:76) at private.com.amazonaws.services.datapipeline.poller.PollWorker.run(PollWorker.java:53) at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.RuntimeException: java.sql.SQLException: Parameter index out of range (43 > number of parameters, which is 42). at private.com.google.common.base.Throwables.propagate(Unknown Source) at amazonaws.datapipeline.connector.SqlOutputConnector.addRecord(SqlOutputConnector.java:93) at amazonaws.datapipeline.connector.SqlOutputConnector.writeRecord(SqlOutputConnector.java:65) at amazonaws.datapipeline.activity.copy.SingleThreadedCopyActivity.processAll(SingleThreadedCopyActivity.java:53) ... 9 more Caused by: java.sql.SQLException: Parameter index out of range (43 > number of parameters, which is 42). at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870) at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3391) at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3376) at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4102) at amazonaws.datapipeline.connector.SqlOutputConnector.addRecord(SqlOutputConnector.java:78) ... 11 more