I have created a pipeline to load data from S3 to RDS mysql instance.I can save the pipeline without any errors but on activation I get the error "No value specified for parameter 1". My online search so far has suggested that the insert statement parameters need to be defined somewhere. If this is correct then how to do so?
The following is the script generated in the process
{
"objects": [
{
"output": {
"ref": "DestinationRDSTable"
},
"input": {
"ref": "S3InputDataLocation"
},
"dependsOn": {
"ref": "RdsMySqlTableCreateActivity"
},
"name": "DataLoadActivity",
"id": "DataLoadActivity",
"runsOn": {
"ref": "Ec2Instance"
},
"type": "CopyActivity"
},
{
"*password": "#{*myRDSPassword}",
"name": "rds_mysql",
"jdbcProperties": "allowMultiQueries=true",
"id": "rds_mysql",
"type": "RdsDatabase",
"rdsInstanceId": "#{myRDSInstanceId}",
"username": "#{myRDSUsername}"
},
{
"instanceType": "t1.micro",
"name": "Ec2Instance",
"actionOnTaskFailure": "terminate",
"securityGroups": "#{myEc2RdsSecurityGrps}",
"id": "Ec2Instance",
"type": "Ec2Resource",
"terminateAfter": "2 Hours"
},
{
"database": {
"ref": "rds_mysql"
},
"name": "RdsMySqlTableCreateActivity",
"runsOn": {
"ref": "Ec2Instance"
},
"id": "RdsMySqlTableCreateActivity",
"type": "SqlActivity",
"script": "#{myRDSTableInsertSql}"
},
{
"database": {
"ref": "rds_mysql"
},
"name": "DestinationRDSTable",
"insertQuery": "#{myRDSTableInsertSql}",
"id": "DestinationRDSTable",
"type": "SqlDataNode",
"table": "#{myRDSTableName}",
"selectQuery": "select * from #{table}"
},
{
"escapeChar": "\\",
"name": "DataFormat1",
"columnSeparator": "|",
"id": "DataFormat1",
"type": "TSV",
"recordSeparator": "\\n"
},
{
"directoryPath": "#{myInputS3Loc}",
"dataFormat": {
"ref": "DataFormat1"
},
"name": "S3InputDataLocation",
"id": "S3InputDataLocation",
"type": "S3DataNode"
},
{
"failureAndRerunMode": "CASCADE",
"resourceRole": "DataPipelineDefaultResourceRole",
"role": "DataPipelineDefaultRole",
"pipelineLogUri": "s3://logs3tords/",
"scheduleType": "ONDEMAND",
"name": "Default",
"id": "Default"
}
],
"parameters": [
{
"description": "RDS MySQL password",
"id": "*myRDSPassword",
"type": "String"
},
{
"watermark": "security group name",
"helpText": "The names of one or more EC2 security groups that have access to the RDS MySQL cluster.",
"description": "RDS MySQL security group(s)",
"isArray": "true",
"optional": "true",
"id": "myEc2RdsSecurityGrps",
"type": "String"
},
{
"description": "RDS MySQL username",
"id": "myRDSUsername",
"type": "String"
},
{
"description": "Input S3 file path",
"id": "myInputS3Loc",
"type": "AWS::S3::ObjectKey"
},
{
"helpText": "The SQL statement to insert data into the RDS MySQL table.",
"watermark": "INSERT INTO #{table} (col1, col2, col3) VALUES(?, ?, ?) ;",
"description": "Insert SQL query",
"id": "myRDSTableInsertSql",
"type": "String"
},
{
"helpText": "The name of an existing table or a new table that will be created based on the create table SQL query parameter below.",
"description": "RDS MySQL table name",
"id": "myRDSTableName",
"type": "String"
},
{
"watermark": "CREATE TABLE pet IF NOT EXISTS (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), gender CHAR(1), birth DATE, death DATE);",
"helpText": "The idempotent SQL statement to create the RDS MySQL table if it does not already exist.",
"description": "Create table SQL query",
"optional": "true",
"id": "myRDSCreateTableSql",
"type": "String"
},
{
"watermark": "DB Instance",
"description": "RDS Instance ID",
"id": "myRDSInstanceId",
"type": "String"
}
],
"values": {
"myRDSInstanceId": "instance name",
"myRDSUsername": "user",
"myRDSTableInsertSql": "Insert into Ten.MD_ip_hp (ID, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, DS ) VALUES(?,?,?,?,?,?,?,?);",
"*myRDSPassword": "password",
"myInputS3Loc": "log location",
"myRDSTableName": "MD_ip_hp"
}
}
UPDATE: So I specified 'script argument' 1 to 8 on the sql activity node which resulted in my error to change to "No value specified for parameter 2". How to now read each number as a different parameter? >:x
