what is the best way of ETL Process In AWS

I have my data in redshift cluster and it's refreshing on a daily basis.

I want have to run a SQL code on a daily basis that will create the table in redshift cluster. So I have to setup the ETL job that will run on a particular time to create the table from SQL code.

I have no idea, what is the best way, I am very new in AWS and have good knowledge of SQL. Can anyone suggest how to proceed?

edited Dec 27 '18 at 21:47

trincot

118k1480111

asked Dec 27 '18 at 15:16

Atul

New contributor

You may check stackoverflow.com/questions/52306194/…
– Sandeep Fatangare
Dec 28 '18 at 7:33

add a comment |

I have my data in redshift cluster and it's refreshing on a daily basis.

I want have to run a SQL code on a daily basis that will create the table in redshift cluster. So I have to setup the ETL job that will run on a particular time to create the table from SQL code.

I have no idea, what is the best way, I am very new in AWS and have good knowledge of SQL. Can anyone suggest how to proceed?

edited Dec 27 '18 at 21:47

trincot

118k1480111

asked Dec 27 '18 at 15:16

Atul

New contributor

You may check stackoverflow.com/questions/52306194/…
– Sandeep Fatangare
Dec 28 '18 at 7:33

add a comment |

I have my data in redshift cluster and it's refreshing on a daily basis.

I want have to run a SQL code on a daily basis that will create the table in redshift cluster. So I have to setup the ETL job that will run on a particular time to create the table from SQL code.

I have no idea, what is the best way, I am very new in AWS and have good knowledge of SQL. Can anyone suggest how to proceed?

edited Dec 27 '18 at 21:47

trincot

118k1480111

asked Dec 27 '18 at 15:16

Atul

New contributor

I have my data in redshift cluster and it's refreshing on a daily basis.

I want have to run a SQL code on a daily basis that will create the table in redshift cluster. So I have to setup the ETL job that will run on a particular time to create the table from SQL code.

I have no idea, what is the best way, I am very new in AWS and have good knowledge of SQL. Can anyone suggest how to proceed?

amazon-web-services amazon-redshift

edited Dec 27 '18 at 21:47

trincot

118k1480111

asked Dec 27 '18 at 15:16

Atul

New contributor

edited Dec 27 '18 at 21:47

trincot

118k1480111

asked Dec 27 '18 at 15:16

Atul

New contributor

edited Dec 27 '18 at 21:47

trincot

118k1480111

edited Dec 27 '18 at 21:47

trincot

118k1480111

edited Dec 27 '18 at 21:47

trincot

118k1480111

asked Dec 27 '18 at 15:16

Atul

New contributor

asked Dec 27 '18 at 15:16

Atul

asked Dec 27 '18 at 15:16

Atul

New contributor

Atul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

You may check stackoverflow.com/questions/52306194/…
– Sandeep Fatangare
Dec 28 '18 at 7:33

add a comment |

You may check stackoverflow.com/questions/52306194/…
– Sandeep Fatangare
Dec 28 '18 at 7:33

You may check stackoverflow.com/questions/52306194/…
– Sandeep Fatangare
Dec 28 '18 at 7:33

add a comment |

1 Answer
1

active

oldest

votes

Short answer: There could be many ways to do it, what you're trying.

Long answer: It could be done any of below mentioned ways in general.

Using any general purpose programming language(java, python, C/C++,.net, etc.)

Using any ready made ETL tools(like pantaho, AWS glue etc)

Other ways

Since you said, you are naive, I would like to explain you simple approach that I used for complex ETL in my past(i.e. plain shell scripts), though think about your use case and weight it against various options I suggested and use the one fits best to you.

Create your shell/batch scripts to run SQL.

Setup a cron job to invoke #1 shell script.

Here goes the example shell script to begin with. Make sure to run beow command, psql command should be installed on one of your EC2 from where you will be connected to Redshift

#!/bin/sh

# example comment!

echo "Executing the create sales table"

psql postgresql://username:password@redshift-url:port/databasename?sslmode=require -c 

    "create table sales(    Colunm1 varchar(55),    Colunm2 varchar(255), updated_at timestamp);"

echo "Sales table created."

This only provides you some pointers to begin with. There are so many pros/cons of every approach and as I said, you must weight all the pros/cons before deciding any approach.

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

Hi Thank you so much for your help, really appreciate your suggestion.
– Atul
Dec 28 '18 at 7:51

I have PostgreSQL in under RDS instances, where i am able to create a database, do i have to install PostgreSQL on my system? or how i will run the cron job? Is there any video available where i can go step by step to reach out the final stage?
– Atul
Dec 28 '18 at 8:00

No psql is client tool, I believe it could be installed without full PostgreSQL database. Here is some pointer. unix.stackexchange.com/questions/249494/… , similarly crontab is very popular and old way of scheduling the jobs, I think check your network administrator or anyone bit familiar with unix. here you go with basis info on cron-tab. tutorialspoint.com/unix_commands/crontab.htm. for additional info, please search on youtube with cron tab you should get lot of good material to begin with.
– Red Boy
Dec 28 '18 at 16:54

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

Atul is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53947207%2fwhat-is-the-best-way-of-etl-process-in-aws%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Short answer: There could be many ways to do it, what you're trying.

Long answer: It could be done any of below mentioned ways in general.

Using any general purpose programming language(java, python, C/C++,.net, etc.)

Using any ready made ETL tools(like pantaho, AWS glue etc)

Other ways

Create your shell/batch scripts to run SQL.

Setup a cron job to invoke #1 shell script.

Here goes the example shell script to begin with. Make sure to run beow command, psql command should be installed on one of your EC2 from where you will be connected to Redshift

#!/bin/sh

# example comment!

echo "Executing the create sales table"

psql postgresql://username:password@redshift-url:port/databasename?sslmode=require -c 

    "create table sales(    Colunm1 varchar(55),    Colunm2 varchar(255), updated_at timestamp);"

echo "Sales table created."

This only provides you some pointers to begin with. There are so many pros/cons of every approach and as I said, you must weight all the pros/cons before deciding any approach.

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

Hi Thank you so much for your help, really appreciate your suggestion.
– Atul
Dec 28 '18 at 7:51

I have PostgreSQL in under RDS instances, where i am able to create a database, do i have to install PostgreSQL on my system? or how i will run the cron job? Is there any video available where i can go step by step to reach out the final stage?
– Atul
Dec 28 '18 at 8:00

No psql is client tool, I believe it could be installed without full PostgreSQL database. Here is some pointer. unix.stackexchange.com/questions/249494/… , similarly crontab is very popular and old way of scheduling the jobs, I think check your network administrator or anyone bit familiar with unix. here you go with basis info on cron-tab. tutorialspoint.com/unix_commands/crontab.htm. for additional info, please search on youtube with cron tab you should get lot of good material to begin with.
– Red Boy
Dec 28 '18 at 16:54

add a comment |

Short answer: There could be many ways to do it, what you're trying.

Long answer: It could be done any of below mentioned ways in general.

Using any general purpose programming language(java, python, C/C++,.net, etc.)

Using any ready made ETL tools(like pantaho, AWS glue etc)

Other ways

Create your shell/batch scripts to run SQL.

Setup a cron job to invoke #1 shell script.

Here goes the example shell script to begin with. Make sure to run beow command, psql command should be installed on one of your EC2 from where you will be connected to Redshift

#!/bin/sh

# example comment!

echo "Executing the create sales table"

psql postgresql://username:password@redshift-url:port/databasename?sslmode=require -c 

    "create table sales(    Colunm1 varchar(55),    Colunm2 varchar(255), updated_at timestamp);"

echo "Sales table created."

This only provides you some pointers to begin with. There are so many pros/cons of every approach and as I said, you must weight all the pros/cons before deciding any approach.

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

Hi Thank you so much for your help, really appreciate your suggestion.
– Atul
Dec 28 '18 at 7:51

I have PostgreSQL in under RDS instances, where i am able to create a database, do i have to install PostgreSQL on my system? or how i will run the cron job? Is there any video available where i can go step by step to reach out the final stage?
– Atul
Dec 28 '18 at 8:00

No psql is client tool, I believe it could be installed without full PostgreSQL database. Here is some pointer. unix.stackexchange.com/questions/249494/… , similarly crontab is very popular and old way of scheduling the jobs, I think check your network administrator or anyone bit familiar with unix. here you go with basis info on cron-tab. tutorialspoint.com/unix_commands/crontab.htm. for additional info, please search on youtube with cron tab you should get lot of good material to begin with.
– Red Boy
Dec 28 '18 at 16:54

add a comment |

Short answer: There could be many ways to do it, what you're trying.

Long answer: It could be done any of below mentioned ways in general.

Using any general purpose programming language(java, python, C/C++,.net, etc.)

Using any ready made ETL tools(like pantaho, AWS glue etc)

Other ways

Create your shell/batch scripts to run SQL.

Setup a cron job to invoke #1 shell script.

Here goes the example shell script to begin with. Make sure to run beow command, psql command should be installed on one of your EC2 from where you will be connected to Redshift

#!/bin/sh

# example comment!

echo "Executing the create sales table"

psql postgresql://username:password@redshift-url:port/databasename?sslmode=require -c 

    "create table sales(    Colunm1 varchar(55),    Colunm2 varchar(255), updated_at timestamp);"

echo "Sales table created."

This only provides you some pointers to begin with. There are so many pros/cons of every approach and as I said, you must weight all the pros/cons before deciding any approach.

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

Short answer: There could be many ways to do it, what you're trying.

Long answer: It could be done any of below mentioned ways in general.

Using any general purpose programming language(java, python, C/C++,.net, etc.)

Using any ready made ETL tools(like pantaho, AWS glue etc)

Other ways

Create your shell/batch scripts to run SQL.

Setup a cron job to invoke #1 shell script.

Here goes the example shell script to begin with. Make sure to run beow command, psql command should be installed on one of your EC2 from where you will be connected to Redshift

#!/bin/sh

# example comment!

echo "Executing the create sales table"

psql postgresql://username:password@redshift-url:port/databasename?sslmode=require -c 

    "create table sales(    Colunm1 varchar(55),    Colunm2 varchar(255), updated_at timestamp);"

echo "Sales table created."

This only provides you some pointers to begin with. There are so many pros/cons of every approach and as I said, you must weight all the pros/cons before deciding any approach.

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

answered Dec 27 '18 at 16:39

Red Boy

2,1052923

Hi Thank you so much for your help, really appreciate your suggestion.
– Atul
Dec 28 '18 at 7:51

I have PostgreSQL in under RDS instances, where i am able to create a database, do i have to install PostgreSQL on my system? or how i will run the cron job? Is there any video available where i can go step by step to reach out the final stage?
– Atul
Dec 28 '18 at 8:00

No psql is client tool, I believe it could be installed without full PostgreSQL database. Here is some pointer. unix.stackexchange.com/questions/249494/… , similarly crontab is very popular and old way of scheduling the jobs, I think check your network administrator or anyone bit familiar with unix. here you go with basis info on cron-tab. tutorialspoint.com/unix_commands/crontab.htm. for additional info, please search on youtube with cron tab you should get lot of good material to begin with.
– Red Boy
Dec 28 '18 at 16:54

add a comment |

Hi Thank you so much for your help, really appreciate your suggestion.
– Atul
Dec 28 '18 at 7:51

I have PostgreSQL in under RDS instances, where i am able to create a database, do i have to install PostgreSQL on my system? or how i will run the cron job? Is there any video available where i can go step by step to reach out the final stage?
– Atul
Dec 28 '18 at 8:00

No psql is client tool, I believe it could be installed without full PostgreSQL database. Here is some pointer. unix.stackexchange.com/questions/249494/… , similarly crontab is very popular and old way of scheduling the jobs, I think check your network administrator or anyone bit familiar with unix. here you go with basis info on cron-tab. tutorialspoint.com/unix_commands/crontab.htm. for additional info, please search on youtube with cron tab you should get lot of good material to begin with.
– Red Boy
Dec 28 '18 at 16:54

Hi Thank you so much for your help, really appreciate your suggestion.
– Atul
Dec 28 '18 at 7:51

I have PostgreSQL in under RDS instances, where i am able to create a database, do i have to install PostgreSQL on my system? or how i will run the cron job? Is there any video available where i can go step by step to reach out the final stage?
– Atul
Dec 28 '18 at 8:00

No psql is client tool, I believe it could be installed without full PostgreSQL database. Here is some pointer. unix.stackexchange.com/questions/249494/… , similarly crontab is very popular and old way of scheduling the jobs, I think check your network administrator or anyone bit familiar with unix. here you go with basis info on cron-tab. tutorialspoint.com/unix_commands/crontab.htm. for additional info, please search on youtube with cron tab you should get lot of good material to begin with.
– Red Boy
Dec 28 '18 at 16:54

add a comment |

Atul is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Atul is a new contributor. Be nice, and check out our Code of Conduct.

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtjtk