How analyse AWS S3 files with Lookerstudio
How analyse AWS S3 files with Lookerstudio
Embedded Files
Looker Studio is good solution when you want to analyse data from google services like GA4, ADS, GCP,ADS HUB,Web Console and etc. It has a native connectors and provides data analysing on the fly. In other hand an organization might store their data from CRM, Warehouses, DB, department surveys in AWS S3 Cloud storage by many reason the main one is costeffectivnes. Think about what are you going to do in case you want understand how website user behavior patterns (GA4) affect to your product sales funnel (CRM data stored AWS s3).
Looker Studio itself doesn't directly connect to and analyze AWS S3 files. S3 is a storage service, and Looker Studio needs your data in a format suitable for analysis, like a relational database or a data warehouse. What can takes huge amount of effort and significantly delay getting necessary inside into a problem you are trying to find answers.
To overcame the challenge I found probably the best solution. This is Looker community connector and Apps Script. So that, the problem breaks downs to 3 steps.
Settle special policy for AWS S3 storage sharing data with apps script fetch method
Develop community connector code using JavaScript
Make this connector available to pull data to Looker report
1 policy for AWS S3 storage
1 policy for AWS S3 storage
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "PublicRead",
"Effect": "Allow",
"Principal": "*", "Action": [
"s3:GetObject",
"s3:GetObjectVersion"
],
"Resource": [
"arn:aws:s3:::<bucket-name>/*" ]
}
]
}
2 community connector
2 community connector
In this example I have made connector to analyse CSV files. Notice, the processing code your are going to write depends of your files data schema.
function getConfig() {
const config = cc.getConfig();
config
.newInfo()
.setId('instructions')
.setText(
'Enter npm package names to fetch their download count. An invalid or blank entry will revert to the default value.'
);
config
.newTextInput()
.setId('fileS3')
.setName(
'Enter url S3 file CSV gzip'
)
return config.build();
}
function getFields() {
var fields = cc.getFields();
var types = cc.FieldType;
var aggregations = cc.AggregationType;
cash.csvdata[0].forEach((v,index) => {
fields
.newDimension()
.setId(index)
.setName('Column'+ index)
.setType(types.TEXT);
})
return fields;
}
// https://developers.google.com/datastudio/connector/reference#getschema
function getSchema(request) {
if (!cash.csvdata.length) {
cash.csvdata = readS3FileCSV(request.configParams.fileS3)
}
return {schema: getFields().build()};
}
function getData(request) {
if (!cash.csvdata.length) {
cash.csvdata = readS3FileCSV(request.configParams.fileS3)
}
const requestedFields = getFields().forIds(
request.fields.map(function(field) {
return field.name;
})
);
let fieldIndexList = requestedFields.asArray().map(r => r.getId())
return {
schema: requestedFields.build(),
rows: formatCSV(cash.getDataByIndexList(fieldIndexList))
};
}
// https://developers.google.com/datastudio/connector/reference#isadminuser
function isAdminUser() {
return false;
}
3 pull data
3 pull data
As you develop your Community Connector, you can use and test it in Looker Studio using the Head Deployment.
Files stored in S3 cloud have CSV format , hence schema have to be described manually
use S3 files data in Looker Report
use S3 files data in Looker Report
Now i have access to CSV files in Looker as sources and can use to create dashboard with automatically updates on-demand
Page updated
Google Sites
Report abuse