Solved

MySQL ETL scipts

Posted on 2016-09-20
11
26 Views
Last Modified: 2016-10-25
can anyone show me some sample ETL scripts to load data from staging tables in MySQL  with error handling and logging etc
0
Comment
Question by:Tara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 250 total points (awarded by participants)
ID: 41808688
The asiest way to import files to mysql is to use the LOAD command from within MySQL server:
LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Open in new window

Ignore 1 line only if the file has headers. More details on the command here:

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

If you want to log the errors then you can use try catch:

https://www.experts-exchange.com/questions/28101010/Is-there-try-catch-block-capability-in-MySQL-and-If-so-how.html
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 250 total points (awarded by participants)
ID: 41808923
Be aware that handlers are only available in stored programs, and that LOAD DATA can not be used in stored programs.

LOAD DATA has no error checking or handling aside from MySQL's natural data validation.  For example, MySQL will generate an error or warning if a field value fails type checking, such as when inserting a string into an integer field.  

LOAD DATA does, however, cause INSERT triggers to fire, which is where you would need your validation/error-handling code.  If you create an INSERT trigger on your destination table, you can leverage the full capability of handlers, as well as other complicated logic to verify the validity of the imported row.  Keep in mind that allowing an error condition to propagate in one of the triggers will behave in the same way as if the error was generated by LOAD DATA.

Finally, the LOCAL keyword impacts how errors during LOAD DATA operations are handled.  When using LOCAL, errors become warnings, and the remainder of the import proceeds.  When not using LOCAL, an error will halt the operation.  LOAD DATA operations are considered atomic, which means a single error will rollback any valid rows found before the error.
0
 

Author Comment

by:Tara
ID: 41809064
Thanks for the responses I am thinking about stored procedures using insert/replace statements from staging tables , similar to the sample code Microsoft has provided with Adventure works
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 41809292
But how do you get data in the staging tables? You still need to import from the files and that you do with LOAD statements. Of course you could use some ETL third party tools that are even free. Try Talend Data Integration for instance:

https://www.talend.com/download/talend-open-studio#t4
0
 

Author Comment

by:Tara
ID: 41809341
I am using php scripts to upload into staging tables .
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41809352
So you are actually asking scripts in PHP? If yes you should add the PHP tag to your question.

If you load data using a programming language then yes, you load data into some staging tables and from there you can do whatever you need in MySQL code. I prefer and use Python, though. It is much simpler to use but PHP, or any other language for that matter, is fine too.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 41809662
If you are using PHP, I recommend you handle your validation there.  MySQL does not have the flexibility that languages like PHP or Python have when it comes to reading files, implementing complicated logic, or reporting issues during operations.
0
 

Author Comment

by:Tara
ID: 41810926
Thank you for your comments! I will close this question
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41810931
You don't have to close it unless you don't award points to anyone, otherwise you just award them and that's it.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41832212
Tara, will you please finish this question? Thanks. No need to close it jut give the award points.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41858226
I split the points:

Zberteoc: 250
Steve Bink: 250
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question