1601345009
While writing a series of articles on how to answer various business questions using SQL, I needed to find public datasets. Wanting to use real data instead of curated Kaggle datasets, I set up my database instances across various cloud platforms. I am sharing these database setup experiences. The first implementation is AWS MySQL.
I will start and end any argument with ‘Free Tier.’
AWS is obviously a popular platform. Not every interaction with AWS is a smooth process, though this experience went well.
While the database instance for MySQL worked as expected, the MySQL Workbench leaves much to be desired. My insert SQL file to build the data rows kept freezing up my app. Once the data was loaded, the query performance from the Workbench to AWS and back was reasonably quick.
#database #mysql #data-science #aws
1651383480
This serverless plugin is a wrapper for amplify-appsync-simulator made for testing AppSync APIs built with serverless-appsync-plugin.
Install
npm install serverless-appsync-simulator
# or
yarn add serverless-appsync-simulator
Usage
This plugin relies on your serverless yml file and on the serverless-offline
plugin.
plugins:
- serverless-dynamodb-local # only if you need dynamodb resolvers and you don't have an external dynamodb
- serverless-appsync-simulator
- serverless-offline
Note: Order is important serverless-appsync-simulator
must go before serverless-offline
To start the simulator, run the following command:
sls offline start
You should see in the logs something like:
...
Serverless: AppSync endpoint: http://localhost:20002/graphql
Serverless: GraphiQl: http://localhost:20002
...
Configuration
Put options under custom.appsync-simulator
in your serverless.yml
file
| option | default | description | | ------------------------ | -------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | | apiKey | 0123456789
| When using API_KEY
as authentication type, the key to authenticate to the endpoint. | | port | 20002 | AppSync operations port; if using multiple APIs, the value of this option will be used as a starting point, and each other API will have a port of lastPort + 10 (e.g. 20002, 20012, 20022, etc.) | | wsPort | 20003 | AppSync subscriptions port; if using multiple APIs, the value of this option will be used as a starting point, and each other API will have a port of lastPort + 10 (e.g. 20003, 20013, 20023, etc.) | | location | . (base directory) | Location of the lambda functions handlers. | | refMap | {} | A mapping of resource resolutions for the Ref
function | | getAttMap | {} | A mapping of resource resolutions for the GetAtt
function | | importValueMap | {} | A mapping of resource resolutions for the ImportValue
function | | functions | {} | A mapping of external functions for providing invoke url for external fucntions | | dynamoDb.endpoint | http://localhost:8000 | Dynamodb endpoint. Specify it if you're not using serverless-dynamodb-local. Otherwise, port is taken from dynamodb-local conf | | dynamoDb.region | localhost | Dynamodb region. Specify it if you're connecting to a remote Dynamodb intance. | | dynamoDb.accessKeyId | DEFAULT_ACCESS_KEY | AWS Access Key ID to access DynamoDB | | dynamoDb.secretAccessKey | DEFAULT_SECRET | AWS Secret Key to access DynamoDB | | dynamoDb.sessionToken | DEFAULT_ACCESS_TOKEEN | AWS Session Token to access DynamoDB, only if you have temporary security credentials configured on AWS | | dynamoDb.* | | You can add every configuration accepted by DynamoDB SDK | | rds.dbName | | Name of the database | | rds.dbHost | | Database host | | rds.dbDialect | | Database dialect. Possible values (mysql | postgres) | | rds.dbUsername | | Database username | | rds.dbPassword | | Database password | | rds.dbPort | | Database port | | watch | - *.graphql
- *.vtl | Array of glob patterns to watch for hot-reloading. |
Example:
custom:
appsync-simulator:
location: '.webpack/service' # use webpack build directory
dynamoDb:
endpoint: 'http://my-custom-dynamo:8000'
Hot-reloading
By default, the simulator will hot-relad when changes to *.graphql
or *.vtl
files are detected. Changes to *.yml
files are not supported (yet? - this is a Serverless Framework limitation). You will need to restart the simulator each time you change yml files.
Hot-reloading relies on watchman. Make sure it is installed on your system.
You can change the files being watched with the watch
option, which is then passed to watchman as the match expression.
e.g.
custom:
appsync-simulator:
watch:
- ["match", "handlers/**/*.vtl", "wholename"] # => array is interpreted as the literal match expression
- "*.graphql" # => string like this is equivalent to `["match", "*.graphql"]`
Or you can opt-out by leaving an empty array or set the option to false
Note: Functions should not require hot-reloading, unless you are using a transpiler or a bundler (such as webpack, babel or typescript), un which case you should delegate hot-reloading to that instead.
Resource CloudFormation functions resolution
This plugin supports some resources resolution from the Ref
, Fn::GetAtt
and Fn::ImportValue
functions in your yaml file. It also supports some other Cfn functions such as Fn::Join
, Fb::Sub
, etc.
Note: Under the hood, this features relies on the cfn-resolver-lib package. For more info on supported cfn functions, refer to the documentation
You can reference resources in your functions' environment variables (that will be accessible from your lambda functions) or datasource definitions. The plugin will automatically resolve them for you.
provider:
environment:
BUCKET_NAME:
Ref: MyBucket # resolves to `my-bucket-name`
resources:
Resources:
MyDbTable:
Type: AWS::DynamoDB::Table
Properties:
TableName: myTable
...
MyBucket:
Type: AWS::S3::Bucket
Properties:
BucketName: my-bucket-name
...
# in your appsync config
dataSources:
- type: AMAZON_DYNAMODB
name: dynamosource
config:
tableName:
Ref: MyDbTable # resolves to `myTable`
Sometimes, some references cannot be resolved, as they come from an Output from Cloudformation; or you might want to use mocked values in your local environment.
In those cases, you can define (or override) those values using the refMap
, getAttMap
and importValueMap
options.
refMap
takes a mapping of resource name to value pairsgetAttMap
takes a mapping of resource name to attribute/values pairsimportValueMap
takes a mapping of import name to values pairsExample:
custom:
appsync-simulator:
refMap:
# Override `MyDbTable` resolution from the previous example.
MyDbTable: 'mock-myTable'
getAttMap:
# define ElasticSearchInstance DomainName
ElasticSearchInstance:
DomainEndpoint: 'localhost:9200'
importValueMap:
other-service-api-url: 'https://other.api.url.com/graphql'
# in your appsync config
dataSources:
- type: AMAZON_ELASTICSEARCH
name: elasticsource
config:
# endpoint resolves as 'http://localhost:9200'
endpoint:
Fn::Join:
- ''
- - https://
- Fn::GetAtt:
- ElasticSearchInstance
- DomainEndpoint
In some special cases you will need to use key-value mock nottation. Good example can be case when you need to include serverless stage value (${self:provider.stage}
) in the import name.
This notation can be used with all mocks - refMap
, getAttMap
and importValueMap
provider:
environment:
FINISH_ACTIVITY_FUNCTION_ARN:
Fn::ImportValue: other-service-api-${self:provider.stage}-url
custom:
serverless-appsync-simulator:
importValueMap:
- key: other-service-api-${self:provider.stage}-url
value: 'https://other.api.url.com/graphql'
This plugin only tries to resolve the following parts of the yml tree:
provider.environment
functions[*].environment
custom.appSync
If you have the need of resolving others, feel free to open an issue and explain your use case.
For now, the supported resources to be automatically resovled by Ref:
are:
Feel free to open a PR or an issue to extend them as well.
External functions
When a function is not defined withing the current serverless file you can still call it by providing an invoke url which should point to a REST method. Make sure you specify "get" or "post" for the method. Default is "get", but you probably want "post".
custom:
appsync-simulator:
functions:
addUser:
url: http://localhost:3016/2015-03-31/functions/addUser/invocations
method: post
addPost:
url: https://jsonplaceholder.typicode.com/posts
method: post
Supported Resolver types
This plugin supports resolvers implemented by amplify-appsync-simulator
, as well as custom resolvers.
From Aws Amplify:
Implemented by this plugin
#set( $cols = [] )
#set( $vals = [] )
#foreach( $entry in $ctx.args.input.keySet() )
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
#set( $discard = $cols.add("$toSnake") )
#if( $util.isBoolean($ctx.args.input[$entry]) )
#if( $ctx.args.input[$entry] )
#set( $discard = $vals.add("1") )
#else
#set( $discard = $vals.add("0") )
#end
#else
#set( $discard = $vals.add("'$ctx.args.input[$entry]'") )
#end
#end
#set( $valStr = $vals.toString().replace("[","(").replace("]",")") )
#set( $colStr = $cols.toString().replace("[","(").replace("]",")") )
#if ( $valStr.substring(0, 1) != '(' )
#set( $valStr = "($valStr)" )
#end
#if ( $colStr.substring(0, 1) != '(' )
#set( $colStr = "($colStr)" )
#end
{
"version": "2018-05-29",
"statements": ["INSERT INTO <name-of-table> $colStr VALUES $valStr", "SELECT * FROM <name-of-table> ORDER BY id DESC LIMIT 1"]
}
#set( $update = "" )
#set( $equals = "=" )
#foreach( $entry in $ctx.args.input.keySet() )
#set( $cur = $ctx.args.input[$entry] )
#set( $regex = "([a-z])([A-Z]+)")
#set( $replacement = "$1_$2")
#set( $toSnake = $entry.replaceAll($regex, $replacement).toLowerCase() )
#if( $util.isBoolean($cur) )
#if( $cur )
#set ( $cur = "1" )
#else
#set ( $cur = "0" )
#end
#end
#if ( $util.isNullOrEmpty($update) )
#set($update = "$toSnake$equals'$cur'" )
#else
#set($update = "$update,$toSnake$equals'$cur'" )
#end
#end
{
"version": "2018-05-29",
"statements": ["UPDATE <name-of-table> SET $update WHERE id=$ctx.args.input.id", "SELECT * FROM <name-of-table> WHERE id=$ctx.args.input.id"]
}
{
"version": "2018-05-29",
"statements": ["UPDATE <name-of-table> set deleted_at=NOW() WHERE id=$ctx.args.id", "SELECT * FROM <name-of-table> WHERE id=$ctx.args.id"]
}
#set ( $index = -1)
#set ( $result = $util.parseJson($ctx.result) )
#set ( $meta = $result.sqlStatementResults[1].columnMetadata)
#foreach ($column in $meta)
#set ($index = $index + 1)
#if ( $column["typeName"] == "timestamptz" )
#set ($time = $result["sqlStatementResults"][1]["records"][0][$index]["stringValue"] )
#set ( $nowEpochMillis = $util.time.parseFormattedToEpochMilliSeconds("$time.substring(0,19)+0000", "yyyy-MM-dd HH:mm:ssZ") )
#set ( $isoDateTime = $util.time.epochMilliSecondsToISO8601($nowEpochMillis) )
$util.qr( $result["sqlStatementResults"][1]["records"][0][$index].put("stringValue", "$isoDateTime") )
#end
#end
#set ( $res = $util.parseJson($util.rds.toJsonString($util.toJson($result)))[1][0] )
#set ( $response = {} )
#foreach($mapKey in $res.keySet())
#set ( $s = $mapKey.split("_") )
#set ( $camelCase="" )
#set ( $isFirst=true )
#foreach($entry in $s)
#if ( $isFirst )
#set ( $first = $entry.substring(0,1) )
#else
#set ( $first = $entry.substring(0,1).toUpperCase() )
#end
#set ( $isFirst=false )
#set ( $stringLength = $entry.length() )
#set ( $remaining = $entry.substring(1, $stringLength) )
#set ( $camelCase = "$camelCase$first$remaining" )
#end
$util.qr( $response.put("$camelCase", $res[$mapKey]) )
#end
$utils.toJson($response)
Variable map support is limited and does not differentiate numbers and strings data types, please inject them directly if needed.
Will be escaped properly: null
, true
, and false
values.
{
"version": "2018-05-29",
"statements": [
"UPDATE <name-of-table> set deleted_at=NOW() WHERE id=:ID",
"SELECT * FROM <name-of-table> WHERE id=:ID and unix_timestamp > $ctx.args.newerThan"
],
variableMap: {
":ID": $ctx.args.id,
## ":TIMESTAMP": $ctx.args.newerThan -- This will be handled as a string!!!
}
}
Requires
Author: Serverless-appsync
Source Code: https://github.com/serverless-appsync/serverless-appsync-simulator
License: MIT License
1595905879
HTML to Markdown
MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.
At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now
ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now
ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now
MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:
Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120
As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.
To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.
Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:
ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph
For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.
#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive
1651319520
Serverless APIGateway Service Proxy
This Serverless Framework plugin supports the AWS service proxy integration feature of API Gateway. You can directly connect API Gateway to AWS services without Lambda.
Run serverless plugin install
in your Serverless project.
serverless plugin install -n serverless-apigateway-service-proxy
Here is a services list which this plugin supports for now. But will expand to other services in the feature. Please pull request if you are intersted in it.
Define settings of the AWS services you want to integrate under custom > apiGatewayServiceProxies
and run serverless deploy
.
Sample syntax for Kinesis proxy in serverless.yml
.
custom:
apiGatewayServiceProxies:
- kinesis: # partitionkey is set apigateway requestid by default
path: /kinesis
method: post
streamName: { Ref: 'YourStream' }
cors: true
- kinesis:
path: /kinesis
method: post
partitionKey: 'hardcordedkey' # use static partitionkey
streamName: { Ref: 'YourStream' }
cors: true
- kinesis:
path: /kinesis/{myKey} # use path parameter
method: post
partitionKey:
pathParam: myKey
streamName: { Ref: 'YourStream' }
cors: true
- kinesis:
path: /kinesis
method: post
partitionKey:
bodyParam: data.myKey # use body parameter
streamName: { Ref: 'YourStream' }
cors: true
- kinesis:
path: /kinesis
method: post
partitionKey:
queryStringParam: myKey # use query string param
streamName: { Ref: 'YourStream' }
cors: true
- kinesis: # PutRecords
path: /kinesis
method: post
action: PutRecords
streamName: { Ref: 'YourStream' }
cors: true
resources:
Resources:
YourStream:
Type: AWS::Kinesis::Stream
Properties:
ShardCount: 1
Sample request after deploying.
curl https://xxxxxxx.execute-api.us-east-1.amazonaws.com/dev/kinesis -d '{"message": "some data"}' -H 'Content-Type:application/json'
Sample syntax for SQS proxy in serverless.yml
.
custom:
apiGatewayServiceProxies:
- sqs:
path: /sqs
method: post
queueName: { 'Fn::GetAtt': ['SQSQueue', 'QueueName'] }
cors: true
resources:
Resources:
SQSQueue:
Type: 'AWS::SQS::Queue'
Sample request after deploying.
curl https://xxxxxx.execute-api.us-east-1.amazonaws.com/dev/sqs -d '{"message": "testtest"}' -H 'Content-Type:application/json'
If you'd like to pass additional data to the integration request, you can do so by including your custom API Gateway request parameters in serverless.yml
like so:
custom:
apiGatewayServiceProxies:
- sqs:
path: /queue
method: post
queueName: !GetAtt MyQueue.QueueName
cors: true
requestParameters:
'integration.request.querystring.MessageAttribute.1.Name': "'cognitoIdentityId'"
'integration.request.querystring.MessageAttribute.1.Value.StringValue': 'context.identity.cognitoIdentityId'
'integration.request.querystring.MessageAttribute.1.Value.DataType': "'String'"
'integration.request.querystring.MessageAttribute.2.Name': "'cognitoAuthenticationProvider'"
'integration.request.querystring.MessageAttribute.2.Value.StringValue': 'context.identity.cognitoAuthenticationProvider'
'integration.request.querystring.MessageAttribute.2.Value.DataType': "'String'"
The alternative way to pass MessageAttribute
parameters is via a request body mapping template.
See the SQS section under Customizing request body mapping templates
Simplified response template customization
You can get a simple customization of the responses by providing a template for the possible responses. The template is assumed to be application/json
.
custom:
apiGatewayServiceProxies:
- sqs:
path: /queue
method: post
queueName: !GetAtt MyQueue.QueueName
cors: true
response:
template:
# `success` is used when the integration response is 200
success: |-
{ "message: "accepted" }
# `clientError` is used when the integration response is 400
clientError: |-
{ "message": "there is an error in your request" }
# `serverError` is used when the integration response is 500
serverError: |-
{ "message": "there was an error handling your request" }
Full response customization
If you want more control over the integration response, you can provide an array of objects for the response
value:
custom:
apiGatewayServiceProxies:
- sqs:
path: /queue
method: post
queueName: !GetAtt MyQueue.QueueName
cors: true
response:
- statusCode: 200
selectionPattern: '2\\d{2}'
responseParameters: {}
responseTemplates:
application/json: |-
{ "message": "accepted" }
The object keys correspond to the API Gateway integration response object.
Sample syntax for S3 proxy in serverless.yml
.
custom:
apiGatewayServiceProxies:
- s3:
path: /s3
method: post
action: PutObject
bucket:
Ref: S3Bucket
key: static-key.json # use static key
cors: true
- s3:
path: /s3/{myKey} # use path param
method: get
action: GetObject
bucket:
Ref: S3Bucket
key:
pathParam: myKey
cors: true
- s3:
path: /s3
method: delete
action: DeleteObject
bucket:
Ref: S3Bucket
key:
queryStringParam: key # use query string param
cors: true
resources:
Resources:
S3Bucket:
Type: 'AWS::S3::Bucket'
Sample request after deploying.
curl https://xxxxxx.execute-api.us-east-1.amazonaws.com/dev/s3 -d '{"message": "testtest"}' -H 'Content-Type:application/json'
Similar to the SQS support, you can customize the default request parameters serverless.yml
like so:
custom:
apiGatewayServiceProxies:
- s3:
path: /s3
method: post
action: PutObject
bucket:
Ref: S3Bucket
cors: true
requestParameters:
# if requestParameters has a 'integration.request.path.object' property you should remove the key setting
'integration.request.path.object': 'context.requestId'
'integration.request.header.cache-control': "'public, max-age=31536000, immutable'"
If you'd like use custom API Gateway request templates, you can do so like so:
custom:
apiGatewayServiceProxies:
- s3:
path: /s3
method: get
action: GetObject
bucket:
Ref: S3Bucket
request:
template:
application/json: |
#set ($specialStuff = $context.request.header.x-special)
#set ($context.requestOverride.path.object = $specialStuff.replaceAll('_', '-'))
{}
Note that if the client does not provide a Content-Type
header in the request, ApiGateway defaults to application/json
.
Added the new customization parameter that lets the user set a custom Path Override in API Gateway other than the {bucket}/{object}
This parameter is optional and if not set, will fall back to {bucket}/{object}
The Path Override will add {bucket}/
automatically in front
Please keep in mind, that key or path.object still needs to be set at the moment (maybe this will be made optional later on with this)
Usage (With 2 Path Parameters (folder and file and a fixed file extension)):
custom:
apiGatewayServiceProxies:
- s3:
path: /s3/{folder}/{file}
method: get
action: GetObject
pathOverride: '{folder}/{file}.xml'
bucket:
Ref: S3Bucket
cors: true
requestParameters:
# if requestParameters has a 'integration.request.path.object' property you should remove the key setting
'integration.request.path.folder': 'method.request.path.folder'
'integration.request.path.file': 'method.request.path.file'
'integration.request.path.object': 'context.requestId'
'integration.request.header.cache-control': "'public, max-age=31536000, immutable'"
This will result in API Gateway setting the Path Override attribute to {bucket}/{folder}/{file}.xml
So for example if you navigate to the API Gatway endpoint /language/en
it will fetch the file in S3 from {bucket}/language/en.xml
Can use greedy, for deeper Folders
The forementioned example can also be shortened by a greedy approach. Thanks to @taylorreece for mentioning this.
custom:
apiGatewayServiceProxies:
- s3:
path: /s3/{myPath+}
method: get
action: GetObject
pathOverride: '{myPath}.xml'
bucket:
Ref: S3Bucket
cors: true
requestParameters:
# if requestParameters has a 'integration.request.path.object' property you should remove the key setting
'integration.request.path.myPath': 'method.request.path.myPath'
'integration.request.path.object': 'context.requestId'
'integration.request.header.cache-control': "'public, max-age=31536000, immutable'"
This will translate for example /s3/a/b/c
to a/b/c.xml
You can get a simple customization of the responses by providing a template for the possible responses. The template is assumed to be application/json
.
custom:
apiGatewayServiceProxies:
- s3:
path: /s3
method: post
action: PutObject
bucket:
Ref: S3Bucket
key: static-key.json
response:
template:
# `success` is used when the integration response is 200
success: |-
{ "message: "accepted" }
# `clientError` is used when the integration response is 400
clientError: |-
{ "message": "there is an error in your request" }
# `serverError` is used when the integration response is 500
serverError: |-
{ "message": "there was an error handling your request" }
Sample syntax for SNS proxy in serverless.yml
.
custom:
apiGatewayServiceProxies:
- sns:
path: /sns
method: post
topicName: { 'Fn::GetAtt': ['SNSTopic', 'TopicName'] }
cors: true
resources:
Resources:
SNSTopic:
Type: AWS::SNS::Topic
Sample request after deploying.
curl https://xxxxxx.execute-api.us-east-1.amazonaws.com/dev/sns -d '{"message": "testtest"}' -H 'Content-Type:application/json'
Simplified response template customization
You can get a simple customization of the responses by providing a template for the possible responses. The template is assumed to be application/json
.
custom:
apiGatewayServiceProxies:
- sns:
path: /sns
method: post
topicName: { 'Fn::GetAtt': ['SNSTopic', 'TopicName'] }
cors: true
response:
template:
# `success` is used when the integration response is 200
success: |-
{ "message: "accepted" }
# `clientError` is used when the integration response is 400
clientError: |-
{ "message": "there is an error in your request" }
# `serverError` is used when the integration response is 500
serverError: |-
{ "message": "there was an error handling your request" }
Full response customization
If you want more control over the integration response, you can provide an array of objects for the response
value:
custom:
apiGatewayServiceProxies:
- sns:
path: /sns
method: post
topicName: { 'Fn::GetAtt': ['SNSTopic', 'TopicName'] }
cors: true
response:
- statusCode: 200
selectionPattern: '2\d{2}'
responseParameters: {}
responseTemplates:
application/json: |-
{ "message": "accepted" }
The object keys correspond to the API Gateway integration response object.
Content Handling and Pass Through Behaviour customization
If you want to work with binary fata, you can not specify contentHandling
and PassThrough
inside the request
object.
custom:
apiGatewayServiceProxies:
- sns:
path: /sns
method: post
topicName: { 'Fn::GetAtt': ['SNSTopic', 'TopicName'] }
request:
contentHandling: CONVERT_TO_TEXT
passThrough: WHEN_NO_TEMPLATES
The allowed values correspond with the API Gateway Method integration for ContentHandling and PassthroughBehavior
Sample syntax for DynamoDB proxy in serverless.yml
. Currently, the supported DynamoDB Operations are PutItem
, GetItem
and DeleteItem
.
custom:
apiGatewayServiceProxies:
- dynamodb:
path: /dynamodb/{id}/{sort}
method: put
tableName: { Ref: 'YourTable' }
hashKey: # set pathParam or queryStringParam as a partitionkey.
pathParam: id
attributeType: S
rangeKey: # required if also using sort key. set pathParam or queryStringParam.
pathParam: sort
attributeType: S
action: PutItem # specify action to the table what you want
condition: attribute_not_exists(Id) # optional Condition Expressions parameter for the table
cors: true
- dynamodb:
path: /dynamodb
method: get
tableName: { Ref: 'YourTable' }
hashKey:
queryStringParam: id # use query string parameter
attributeType: S
rangeKey:
queryStringParam: sort
attributeType: S
action: GetItem
cors: true
- dynamodb:
path: /dynamodb/{id}
method: delete
tableName: { Ref: 'YourTable' }
hashKey:
pathParam: id
attributeType: S
action: DeleteItem
cors: true
resources:
Resources:
YourTable:
Type: AWS::DynamoDB::Table
Properties:
TableName: YourTable
AttributeDefinitions:
- AttributeName: id
AttributeType: S
- AttributeName: sort
AttributeType: S
KeySchema:
- AttributeName: id
KeyType: HASH
- AttributeName: sort
KeyType: RANGE
ProvisionedThroughput:
ReadCapacityUnits: 1
WriteCapacityUnits: 1
Sample request after deploying.
curl -XPUT https://xxxxxxx.execute-api.us-east-1.amazonaws.com/dev/dynamodb/<hashKey>/<sortkey> \
-d '{"name":{"S":"john"},"address":{"S":"xxxxx"}}' \
-H 'Content-Type:application/json'
Sample syntax for EventBridge proxy in serverless.yml
.
custom:
apiGatewayServiceProxies:
- eventbridge: # source and detailType are hardcoded; detail defaults to POST body
path: /eventbridge
method: post
source: 'hardcoded_source'
detailType: 'hardcoded_detailType'
eventBusName: { Ref: 'YourBusName' }
cors: true
- eventbridge: # source and detailType as path parameters
path: /eventbridge/{detailTypeKey}/{sourceKey}
method: post
detailType:
pathParam: detailTypeKey
source:
pathParam: sourceKey
eventBusName: { Ref: 'YourBusName' }
cors: true
- eventbridge: # source, detail, and detailType as body parameters
path: /eventbridge/{detailTypeKey}/{sourceKey}
method: post
detailType:
bodyParam: data.detailType
source:
bodyParam: data.source
detail:
bodyParam: data.detail
eventBusName: { Ref: 'YourBusName' }
cors: true
resources:
Resources:
YourBus:
Type: AWS::Events::EventBus
Properties:
Name: YourEventBus
Sample request after deploying.
curl https://xxxxxxx.execute-api.us-east-1.amazonaws.com/dev/eventbridge -d '{"message": "some data"}' -H 'Content-Type:application/json'
To set CORS configurations for your HTTP endpoints, simply modify your event configurations as follows:
custom:
apiGatewayServiceProxies:
- kinesis:
path: /kinesis
method: post
streamName: { Ref: 'YourStream' }
cors: true
Setting cors to true assumes a default configuration which is equivalent to:
custom:
apiGatewayServiceProxies:
- kinesis:
path: /kinesis
method: post
streamName: { Ref: 'YourStream' }
cors:
origin: '*'
headers:
- Content-Type
- X-Amz-Date
- Authorization
- X-Api-Key
- X-Amz-Security-Token
- X-Amz-User-Agent
allowCredentials: false
Configuring the cors property sets Access-Control-Allow-Origin, Access-Control-Allow-Headers, Access-Control-Allow-Methods,Access-Control-Allow-Credentials headers in the CORS preflight response. To enable the Access-Control-Max-Age preflight response header, set the maxAge property in the cors object:
custom:
apiGatewayServiceProxies:
- kinesis:
path: /kinesis
method: post
streamName: { Ref: 'YourStream' }
cors:
origin: '*'
maxAge: 86400
If you are using CloudFront or another CDN for your API Gateway, you may want to setup a Cache-Control header to allow for OPTIONS request to be cached to avoid the additional hop.
To enable the Cache-Control header on preflight response, set the cacheControl property in the cors object:
custom:
apiGatewayServiceProxies:
- kinesis:
path: /kinesis
method: post
streamName: { Ref: 'YourStream' }
cors:
origin: '*'
headers:
- Content-Type
- X-Amz-Date
- Authorization
- X-Api-Key
- X-Amz-Security-Token
- X-Amz-User-Agent
allowCredentials: false
cacheControl: 'max-age=600, s-maxage=600, proxy-revalidate' # Caches on browser and proxy for 10 minutes and doesnt allow proxy to serve out of date content
You can pass in any supported authorization type:
custom:
apiGatewayServiceProxies:
- sqs:
path: /sqs
method: post
queueName: { 'Fn::GetAtt': ['SQSQueue', 'QueueName'] }
cors: true
# optional - defaults to 'NONE'
authorizationType: 'AWS_IAM' # can be one of ['NONE', 'AWS_IAM', 'CUSTOM', 'COGNITO_USER_POOLS']
# when using 'CUSTOM' authorization type, one should specify authorizerId
# authorizerId: { Ref: 'AuthorizerLogicalId' }
# when using 'COGNITO_USER_POOLS' authorization type, one can specify a list of authorization scopes
# authorizationScopes: ['scope1','scope2']
resources:
Resources:
SQSQueue:
Type: 'AWS::SQS::Queue'
Source: AWS::ApiGateway::Method docs
You can indicate whether the method requires clients to submit a valid API key using private
flag:
custom:
apiGatewayServiceProxies:
- sqs:
path: /sqs
method: post
queueName: { 'Fn::GetAtt': ['SQSQueue', 'QueueName'] }
cors: true
private: true
resources:
Resources:
SQSQueue:
Type: 'AWS::SQS::Queue'
which is the same syntax used in Serverless framework.
Source: Serverless: Setting API keys for your Rest API
Source: AWS::ApiGateway::Method docs
By default, the plugin will generate a role with the required permissions for each service type that is configured.
You can configure your own role by setting the roleArn
attribute:
custom:
apiGatewayServiceProxies:
- sqs:
path: /sqs
method: post
queueName: { 'Fn::GetAtt': ['SQSQueue', 'QueueName'] }
cors: true
roleArn: # Optional. A default role is created when not configured
Fn::GetAtt: [CustomS3Role, Arn]
resources:
Resources:
SQSQueue:
Type: 'AWS::SQS::Queue'
CustomS3Role:
# Custom Role definition
Type: 'AWS::IAM::Role'
The plugin allows one to specify which parameters the API Gateway method accepts.
A common use case is to pass custom data to the integration request:
custom:
apiGatewayServiceProxies:
- sqs:
path: /sqs
method: post
queueName: { 'Fn::GetAtt': ['SqsQueue', 'QueueName'] }
cors: true
acceptParameters:
'method.request.header.Custom-Header': true
requestParameters:
'integration.request.querystring.MessageAttribute.1.Name': "'custom-Header'"
'integration.request.querystring.MessageAttribute.1.Value.StringValue': 'method.request.header.Custom-Header'
'integration.request.querystring.MessageAttribute.1.Value.DataType': "'String'"
resources:
Resources:
SqsQueue:
Type: 'AWS::SQS::Queue'
Any published SQS message will have the Custom-Header
value added as a message attribute.
If you'd like to add content types or customize the default templates, you can do so by including your custom API Gateway request mapping template in serverless.yml
like so:
# Required for using Fn::Sub
plugins:
- serverless-cloudformation-sub-variables
custom:
apiGatewayServiceProxies:
- kinesis:
path: /kinesis
method: post
streamName: { Ref: 'MyStream' }
request:
template:
text/plain:
Fn::Sub:
- |
#set($msgBody = $util.parseJson($input.body))
#set($msgId = $msgBody.MessageId)
{
"Data": "$util.base64Encode($input.body)",
"PartitionKey": "$msgId",
"StreamName": "#{MyStreamArn}"
}
- MyStreamArn:
Fn::GetAtt: [MyStream, Arn]
It is important that the mapping template will return a valid
application/json
string
Source: How to connect SNS to Kinesis for cross-account delivery via API Gateway
Customizing SQS request templates requires us to force all requests to use an application/x-www-form-urlencoded
style body. The plugin sets the Content-Type
header to application/x-www-form-urlencoded
for you, but API Gateway will still look for the template under the application/json
request template type, so that is where you need to configure you request body in serverless.yml
:
custom:
apiGatewayServiceProxies:
- sqs:
path: /{version}/event/receiver
method: post
queueName: { 'Fn::GetAtt': ['SqsQueue', 'QueueName'] }
request:
template:
application/json: |-
#set ($body = $util.parseJson($input.body))
Action=SendMessage##
&MessageGroupId=$util.urlEncode($body.event_type)##
&MessageDeduplicationId=$util.urlEncode($body.event_id)##
&MessageAttribute.1.Name=$util.urlEncode("X-Custom-Signature")##
&MessageAttribute.1.Value.DataType=String##
&MessageAttribute.1.Value.StringValue=$util.urlEncode($input.params("X-Custom-Signature"))##
&MessageBody=$util.urlEncode($input.body)
Note that the ##
at the end of each line is an empty comment. In VTL this has the effect of stripping the newline from the end of the line (as it is commented out), which makes API Gateway read all the lines in the template as one line.
Be careful when mixing additional requestParameters
into your SQS endpoint as you may overwrite the integration.request.header.Content-Type
and stop the request template from being parsed correctly. You may also unintentionally create conflicts between parameters passed using requestParameters
and those in your request template. Typically you should only use the request template if you need to manipulate the incoming request body in some way.
Your custom template must also set the Action
and MessageBody
parameters, as these will not be added for you by the plugin.
When using a custom request body, headers sent by a client will no longer be passed through to the SQS queue (PassthroughBehavior
is automatically set to NEVER
). You will need to pass through headers sent by the client explicitly in the request body. Also, any custom querystring parameters in the requestParameters
array will be ignored. These also need to be added via the custom request body.
Similar to the Kinesis support, you can customize the default request mapping templates in serverless.yml
like so:
# Required for using Fn::Sub
plugins:
- serverless-cloudformation-sub-variables
custom:
apiGatewayServiceProxies:
- kinesis:
path: /sns
method: post
topicName: { 'Fn::GetAtt': ['SNSTopic', 'TopicName'] }
request:
template:
application/json:
Fn::Sub:
- "Action=Publish&Message=$util.urlEncode('This is a fixed message')&TopicArn=$util.urlEncode('#{MyTopicArn}')"
- MyTopicArn: { Ref: MyTopic }
It is important that the mapping template will return a valid
application/x-www-form-urlencoded
string
Source: Connect AWS API Gateway directly to SNS using a service integration
You can customize the response body by providing mapping templates for success, server errors (5xx) and client errors (4xx).
Templates must be in JSON format. If a template isn't provided, the integration response will be returned as-is to the client.
custom:
apiGatewayServiceProxies:
- kinesis:
path: /kinesis
method: post
streamName: { Ref: 'MyStream' }
response:
template:
success: |
{
"success": true
}
serverError: |
{
"success": false,
"errorMessage": "Server Error"
}
clientError: |
{
"success": false,
"errorMessage": "Client Error"
}
Author: Serverless-operations
Source Code: https://github.com/serverless-operations/serverless-apigateway-service-proxy
License:
1595781840
MySQL does not limit the number of slaves that you can connect to the master server in a replication topology. However, as the number of slaves increases, they will have a toll on the master resources because the binary logs will need to be served to different slaves working at different speeds. If the data churn on the master is high, the serving of binary logs alone could saturate the network interface of the master.
A classic solution for this problem is to deploy a binlog server – an intermediate proxy server that sits between the master and its slaves. The binlog server is set up as a slave to the master, and in turn, acts as a master to the original set of slaves. It receives binary log events from the master, does not apply these events, but serves them to all the other slaves. This way, the load on the master is tremendously reduced, and at the same time, the binlog server serves the binlogs more efficiently to slaves since it does not have to do any other database server processing.
Ripple is an open source binlog server developed by Pavel Ivanov. A blog post from Percona, titled MySQL Ripple: The First Impression of a MySQL Binlog Server, gives a very good introduction to deploying and using Ripple. I had an opportunity to explore Ripple in some more detail and wanted to share my observations through this post.
Ripple supports only GTID mode, and not file and position-based replication. If your master is running in non-GTID mode, you will get this error from Ripple:
Failed to read packet: Got error reading packet from server: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.
You can specify Server_id and UUID for the ripple server using the cmd line options: -ripple_server_id and -ripple_server_uuid
Both are optional parameters, and if not specified, Ripple will use the default server_id=112211 and uuid will be auto generated.
While connecting to the master, you can specify the replication user and password using the command line options:
-ripple_master_user and -ripple_master_password
You can use the command line options -ripple_server_ports and -ripple_server_address to specify the connection end points for the Ripple server. Ensure to specify the network accessible hostname or IP address of your Ripple server as the -rippple_server_address. Otherwise, by default, Ripple will bind to localhost and hence you will not be able to connect to it remotely.
You can use the CHANGE MASTER TO command to connect your slaves to replicate from the Ripple server.
To ensure that Ripple can authenticate the password that you use to connect to it, you need to start Ripple by specifying the option -ripple_server_password_hash
For example, if you start the ripple server with the command:
rippled -ripple_datadir=./binlog_server -ripple_master_address= <master ip> -ripple_master_port=3306 -ripple_master_user=repl -ripple_master_password='password' -ripple_server_ports=15000 -ripple_server_address='172.31.23.201' -ripple_server_password_hash='EF8C75CB6E99A0732D2DE207DAEF65D555BDFB8E'
you can use the following CHANGE MASTER TO command to connect from the slave:
CHANGE MASTER TO master_host='172.31.23.201', master_port=15000, master_password=’XpKWeZRNH5#satCI’, master_user=’rep’
Note that the password hash specified for the Ripple server corresponds to the text password used in the CHANGE MASTER TO command. Currently, Ripple does not authenticate based on the usernames and accepts any non-empty username as long as the password matches.
Exploring MySQL Binlog Server - Ripple
It’s possible to monitor and manage the Ripple server using the MySQL protocol from any standard MySQL client. There are a limited set of commands that are supported which you can see directly in the source code on the mysql-ripple GitHub page.
Some of the useful commands are:
SELECT @@global.gtid_executed;
– To see the GTID SET of the Ripple server based on its downloaded binary logs.STOP SLAVE;
– To disconnect the Ripple server from the master.START SLAVE;
– To connect the Ripple server to the master.#cloud #database #developer #high availability #mysql #performance #binary logs #gtid replication #mysql binlog #mysql protocol #mysql ripple #mysql server #parallel threads #proxy server #replication topology #ripple server
1619064480
You’re reading this blog so I am hoping that you already know what is MySQL and what we want to achieve in this blog. So, starting right away with all the steps:
#mysql #database #aws #mysql-workbench