· 8 years ago · Apr 27, 2018, 07:56 PM
1{
2 "objects": [
3 {
4 "*password": "#{*myRDSPassword}",
5 "name": "rds_mysql",
6 "jdbcProperties": "allowMultiQueries=true",
7 "id": "rds_mysql",
8 "type": "RdsDatabase",
9 "rdsInstanceId": "#{myRDSInstanceId}",
10 "username": "#{myRDSUsername}"
11 },
12 {
13 "role": "DataPipelineDefaultRole",
14 "subject": "Created-Table-Pipe",
15 "name": "CompletedTableCreation",
16 "id": "ActionId_Naogr",
17 "message": "table created.",
18 "type": "SnsAlarm",
19 "topicArn": "yourarntopoc"
20 },
21 {
22 "name": "DataFormat1",
23 "id": "DataFormat1",
24 "type": "CSV"
25 },
26 {
27 "failureAndRerunMode": "CASCADE",
28 "resourceRole": "DataPipelineDefaultResourceRole",
29 "role": "DataPipelineDefaultRole",
30 "pipelineLogUri": "s3://yourbucket/logs/",
31 "scheduleType": "ONDEMAND",
32 "name": "Default",
33 "id": "Default"
34 },
35 {
36 "output": {
37 "ref": "DestinationRDSTable"
38 },
39 "input": {
40 "ref": "S3InputDataLocation"
41 },
42 "dependsOn": {
43 "ref": "RdsMySqlTableCreateActivity"
44 },
45 "name": "DataLoadActivity",
46 "id": "DataLoadActivity",
47 "runsOn": {
48 "ref": "Ec2Instance"
49 },
50 "type": "CopyActivity",
51 "onSuccess": {
52 "ref": "ActionId_oZ7lt"
53 }
54 },
55 {
56 "role": "DataPipelineDefaultRole",
57 "subject": "DataPipeLine-CompleteS3Copy",
58 "name": "CompletedS3SQLCopy",
59 "id": "ActionId_oZ7lt",
60 "message": "check pipeline ",
61 "type": "SnsAlarm",
62 "topicArn": "yourtopicArnhere"
63 },
64 {
65 "database": {
66 "ref": "rds_mysql"
67 },
68 "name": "DestinationRDSTable",
69 "insertQuery": "#{myRDSTableInsertSql}",
70 "id": "DestinationRDSTable",
71 "type": "SqlDataNode",
72 "table": "#{myRDSTableName}",
73 "selectQuery": "select * from #{table}"
74 },
75 {
76 "database": {
77 "ref": "rds_mysql"
78 },
79 "name": "RdsMySqlTableCreateActivity",
80 "runsOn": {
81 "ref": "Ec2Instance"
82 },
83 "id": "RdsMySqlTableCreateActivity",
84 "type": "SqlActivity",
85 "script": "#{myRDSCreateTableSql}",
86 "onSuccess": {
87 "ref": "ActionId_Naogr"
88 }
89 },
90 {
91 "instanceType": "t1.micro",
92 "name": "Ec2Instance",
93 "actionOnTaskFailure": "terminate",
94 "securityGroups": [
95 "rds-launch-wizard",
96 "default"
97 ],
98 "id": "Ec2Instance",
99 "type": "Ec2Resource",
100 "terminateAfter": "1 Day"
101 },
102 {
103 "directoryPath": "#{myInputS3Loc}",
104 "dataFormat": {
105 "ref": "DataFormat1"
106 },
107 "name": "S3InputDataLocation",
108 "id": "S3InputDataLocation",
109 "type": "S3DataNode"
110 }
111 ],
112 "parameters": [
113 {
114 "description": "RDS MySQL password",
115 "id": "*myRDSPassword",
116 "type": "String"
117 },
118 {
119 "watermark": "security group name",
120 "helpText": "The names of one or more EC2 security groups that have access to the RDS MySQL cluster.",
121 "description": "RDS MySQL security group(s)",
122 "isArray": "true",
123 "optional": "true",
124 "id": "myEc2RdsSecurityGrps",
125 "type": "String"
126 },
127 {
128 "description": "RDS MySQL username",
129 "id": "myRDSUsername",
130 "type": "String"
131 },
132 {
133 "description": "Input S3 file path",
134 "id": "myInputS3Loc",
135 "type": "AWS::S3::ObjectKey"
136 },
137 {
138 "helpText": "The SQL statement to insert data into the RDS MySQL table.",
139 "watermark": "INSERT INTO #{table} (col1, col2, col3) VALUES(?, ?, ?) ;",
140 "description": "Insert SQL query",
141 "id": "myRDSTableInsertSql",
142 "type": "String"
143 },
144 {
145 "helpText": "The name of an existing table or a new table that will be created based on the create table SQL query parameter below.",
146 "description": "RDS MySQL table name",
147 "id": "myRDSTableName",
148 "type": "String"
149 },
150 {
151 "watermark": "CREATE TABLE pet IF NOT EXISTS (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), gender CHAR(1), birth DATE, death DATE);",
152 "helpText": "The idempotent SQL statement to create the RDS MySQL table if it does not already exist.",
153 "description": "Create table SQL query",
154 "optional": "true",
155 "id": "myRDSCreateTableSql",
156 "type": "String"
157 },
158 {
159 "watermark": "DB Instance",
160 "description": "RDS Instance ID",
161 "id": "myRDSInstanceId",
162 "type": "String"
163 }
164 ],
165 "values": {
166 "myRDSInstanceId": "yourdbhere",
167 "myRDSUsername": "yourdbusername",
168 "myRDSTableInsertSql": "INSERT INTO your_table(\nUserId,\nName,\ndOB,\ncityStateZip ) VALUES(?,?,?,?)",
169 "*myRDSPassword": "yourpassword",
170 "myEc2RdsSecurityGrps": "sg-yourgroup",
171 "myRDSCreateTableSql": "CREATE TABLE IF NOT EXISTS your_table(\n UserId VARCHAR(255),\n Name VARCHAR(255),\n dOB VARCHAR(255),\n cityStateZip VARCHAR(255)\n);",
172 "myInputS3Loc": "s3://yourownfile.csv",
173 "myRDSTableName": "yourtablename"
174 }
175}