December 8, 2013 at 3:23 pm
Comments posted to this topic are about the item A few reflections on RBAR by a weary application developer
December 8, 2013 at 3:27 pm
Hi everyone, I enjoyed writing this article and I appreciate the time that you took to read it. I thank you for your comments and I will respond to them all. Have a great day--George
December 8, 2013 at 8:00 pm
But, as a DBA, I would have made sure that the data was clean before attempting an import!:-)
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 8, 2013 at 11:16 pm
I agree. Don't let the bad data get to the database.
I get data files from clients every morning, and they have proven that I can't trust their data.
My strategy is to preprocess the files in Powershell first to make sure the file is clean, and then let the database import it.
I end up with a clean file, plus a name and shame log file to give the client so they know that yes, after the fiftieth time I've warned them, they still aren't giving me a properly formatted CSV file.
December 8, 2013 at 11:43 pm
Hurt.Bill (12/8/2013)
I agree. Don't let the bad data get to the database.
Hey there. I agree as well, but, "bad" data is not just a matter of parsing the source file and making sure those values are valid for the destination datatypes. There can be issues with FK violations as well as CHECK CONSTRAINT and UNIQUE CONSTRAINT violations, or other issues that violate business rules for the data that are being checked via Triggers, etc.
HOWEVER, that still doesn't mean you need RBAR to do proper, pin-point identification of erroneous rows. Whatever ways the data can be "bad" in can be tested for prior to moving the data from the Staging table to the Destination table. Queries can be run against the Staging table to find rows that meet one or more error conditions and flagged as invalid. Then it can be determined whether to skip the invalid rows and import the rest or fail the import if even a single row is flagged.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 9, 2013 at 12:04 am
I agree with what you're saying here. I guess I wasn't explicit that I'm only using Powershell to find rows that would cause import to the staging table to fail. Records that my import tool would consider to have an invalid format, like rows that don't have the same number of data fields as the header has column names.
All of the other things like violation of business rules and bad data types are handled in scrubbing procedures just like you describe.
I'm sure RBAR worked for the purposes of the article scenario just fine, but once the data is in the DB I work just as hard to avoid it in scrubbing as I do anywhere else.
December 9, 2013 at 1:23 am
Nice article.
One remark though: using SSIS doesn't necessarily mean RBAR.
You can stream the data into a bulk load just fine (as fast as the join) and still find the erroneous row.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 9, 2013 at 2:12 am
Consider a daily data file import, 60k rows. The data is loaded, of course, row by agonizing row, into a temp table. The mighty DBA, with his scimitar, strikes a mighty blow and updates the destination tables in one enormous, amazing join. The update runs in production in under three minutes.
Yay! Everyone cries! We weep with the endless possibilities of mighty set based queries!
The DBA declares his work complete, shoulders his mighty scimitar, and departs for the land of milk and honey. And then one day, of course, the daily data import fails. Information from the SQL error about precisely what failed is sparse. None of the developers can get the mighty join to work, and nobody can figure out what is wrong. The person from the helpdesk says that the server probably ran out of memory.
As you can imagine, the tale of this misguided deed that violates all that is holy in the world of set-based code, reaches the ears of the SQL gods. To leave not even a breadcrumb of hope in the form of an encouraging message is sacrilege. The SQL gods all very quickly agree that the false DBA that salted the Earth in such a fashion must be found and punished. Equally as quick, they also realize that they must come to the aid of their followers and send a True DBA from the Anti-RBAR Alliance to right the wrong done to the people and to repair the insult to both data and technique.
The True DBA arrives without flash or flare and does something that no false DBA will do. He listens to the people and asks questions of their plight and does so without the glint of a scimitar. He asks how it is possible that the fruits of labor continue to appear on the tables albeit much slower than he knows is possible and hears of the weakling who so boldly took ownership of the problem…
One of the weakling developers pulls out his penknife and whittles out an SSIS package. Of course, it iterates through the input table row by agonizing row. But here is the deal: it processes until it abends. It fails on the offending record and writes out a meaningful error message. After the problem is fixed, the package can be safely restarted.
The DBA seeks out the weakling and confronts him in the most gentle of manner. "Thoust are not weak and certainly not cowardly. Thou tooketh (heh... I love that word) ownership of the dire problem that was not yours, one that could have brought great harm upon yourself had you been any less successful in your honorable quest. Come. Explain to me what you have wrought and together we shall slay the slothful beast you accidently released from the bowels of the server."
The weakling slowly and thoroughly explained all to the attentive and concerned True DBA as they quaffed much ale together in the land of “sdraillib” while minstrels sang tales of their plight (ed: ok... it was karaoke night). The True DBA and the weakling began forming a plan on the folded skins of the “elatkcoc snikpan” beast and agreed to attack the sloth that very night.
Arriving at the den of data, the fetid smell of RBAR sloth was almost more than one could bear. The weakling turned to the True DBA with great fear in his eyes and with a quivering voice beseeched the True DBA. “I have great faith in thee and thy plan, mi lord, but I have neither the weapon nor the knowledge to carry it out”. With that, the True DBA waved his hands and the portal of knowledge known as “Skoob Enilno” opened before them. As if struck by lightning, the weakling could suddenly see the plan materialize before his eyes. He turned to the True DBA with a look of understanding on his face.
The True DBA spoke softly as he handed the weakling a tiny penknife smaller than the one first he used. “Through the “Skoob Enilno”, yea have learned of the simple power of “Tresni Klub” and its myriad “Sehctiws”. Align them properly for the task at hand and the enemies of the “daol” will be sequestered with reasons apparent. Now, , go forth and Divide’n’Conquer. I will be at your side but it is you who must do this for today, thouest art an accidental DBA and tomorrow, with your newfound enlightenment, you will join the Anit-RBAR alliance as a True DBA, savior of data, servers, deadlines, and weekends with the kids. I warn you, though, although your “seganam” will praise you, you must remain humble and helpful or you will be subject to the hatred of the very people that should follow your lead.
The weaking entered the den of the RBAR sloth. There was no noise during the battle as the weakling felled the beast before it could reiterate with a single blow of new knowledge and the power of the newly learned weapons that were always within his reach. The only way that anyone knew that the short battle was over and successful was by the sudden and repeated appearance of correct data.
Having fulfilled his task of mentorship, the True DBA turned away from the newly guilded True DBA and offered one more gift of knowledge as he calmly walked away. “Remember that such RBAR sloths frequently appear especially in the presence of “Roop Seludehcs”, which always seem present. When you are in fear, summon the “Skoob Enilno” to guide you in building your weapons and remember that the appearance of such sloths shall not be blamed on any one man. They are innocent for they know not what they do. Expose such innocents to the light of “Skoob Enilno” and, in your darkest hours, turn to the one source of knowledge even more powerful, the “Lartnec Revreslqs”. Welcome to the battle known as daily, my young friend.”
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2013 at 2:54 am
Awesome response Jeff! 😀
December 9, 2013 at 3:37 am
"Always do what makes sense"
I'm not so sure about the example given in the article, but that is sound advice. My rule of thumb is, if you need to handle rows individually, then rebar makes sense; else not.
December 9, 2013 at 3:51 am
Stephen Hirsch (12/9/2013)
"Always do what makes sense"I'm not so sure about the example given in the article, but that is sound advice. My rule of thumb is, if you need to handle rows individually, then rebar makes sense; else not.
Unless you can handle the individual rows with windowing functions...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 9, 2013 at 3:53 am
Good grief, I just had to RBAR that response from Jeff. I was hoping for a "SQL" version: the True DBA fixed it.
December 9, 2013 at 4:14 am
Koen Verbeeck (12/9/2013)
Nice article.One remark though: using SSIS doesn't necessarily mean RBAR.
You can stream the data into a bulk load just fine (as fast as the join) and still find the erroneous row.
Big +1 here
December 9, 2013 at 5:26 am
What???????????????
December 9, 2013 at 7:06 am
I didn't see any explanation on why RBAR is so slow, so rated this article 1 star.
Viewing 15 posts - 1 through 15 (of 116 total)
You must be logged in to reply to this topic. Login to reply