Peanut Butter and Chocolate: Integrating Hadoop with SQL Server

on YouTube ↗59m

" summary

Shows concrete recipes to move data: use Sqoop for bulk import/export, a NuGet-based .NET file-maintenance ETL that copies HDFS files into SQL Server in ~50 lines, and SSIS 2016 connection managers to read/write HDFS. Demonstrates linked servers (ODBC hive driver, 8 KB/32K varchar limits, default 10,000 row estimate) and PolyBase (predicate pushdown, collectable stats, compute nodes for parallelism). Runs demos with 777-row sample files and mentions Kafka as an alternate stream option.

AI-written summary · about

" chapters (19)

1. 0:00Start
2. 4:35Five Methods of Tying Together Hadoop and Sequel Server
3. 4:48Ambari
4. 6:14Overview of the Health of the Environment
5. 18:34Nuget Packages
6. 27:44Hadoop Connection Manager
7. 28:53Data Flow Task
8. 31:40Retrieve from Hdfs
9. 35:23Using a Linked Server
10. 37:49Create a Link Server
11. 45:23Parallelized Sequel Server
12. 47:06What that Lets Me Do Is Send Data Back into these Smaller Compute Nodes Pull It into Temp Tables Basically Do As Much of the Processing as It Can Here and Then Send Up the Small Data Set Up into My Head Node Where I Finish Processing Where I Do all Final Work and I Send that Data Out to the User in Other Words this Is a Massive Parallel Processing for Sequel Server It Only Works When You'Re Working with a Hadoop Cluster but Cross My Fingers I'M Hoping that the Poly Based Team Is Able To Get this To Work with Other Queries so that Sequel Server Becomes a Massive Parallel Processing System Just like a New Cluster Would Be There Are a Lot of Problems That That Help Solve
13. 49:17I Am Going To Want To Create a Poly Based Table in Order To Do that I'Ve Got To Do some Prep Work First the First Thing That I Need To Do Is Create an External Data Source That's Saying Where Does the Data Live Data Is Not GonNa Live in Sequel Server Sequel Server Is GonNa Have a Pointer to the Data but It Won't Actually Host the Data the Data Will Be Hosted in a Hadoop Cluster and the Location Will Be this Now I Left this in this Is the Version That's Available When You Grab the Slides
14. 49:59And They By Default Hard Code That Url Sandbox Hortonworks Com in Reality Mine Is Cluster Ii No It's on Port 8000 That Is the Mapreduce Port It's a Configurable Port but that's Default It's a Standard with Hadoop the Resource Manager Is Necessary for Predicate Push Down See What Predicates Push Down Does Essentially Is It Decides I Need To Push Down this Predicate in Other Words I Need To Generate a Mapreduce Job over Here the Alternative Is I Don't Push a Predicate I Just Stream the Data Back this Way Well in Order To Create Mapreduce Jobs the Poly Based Engine Needs To Communicate with a Resource Manager Yarn
15. 50:17The Resource Manager Is Necessary for Predicate Push Down See What Predicates Push Down Does Essentially Is It Decides I Need To Push Down this Predicate in Other Words I Need To Generate a Mapreduce Job over Here the Alternative Is I Don't Push a Predicate I Just Stream the Data Back this Way Well in Order To Create Mapreduce Jobs the Poly Based Engine Needs To Communicate with a Resource Manager Yarn so It Does So Typically that's the Default Port Eight Zero Five Zero the Resource Manager Location Is Not Required but if You Do Not Include It You Will Never Be Able To Do Mapreduce Jobs You Will Only Be Able To Pull Data I Have a Data Source I Need a File Format As Well
16. 51:46I Can Just Make It a Regular Dbo Table Here's Where It Gets Weird the Location Is My Poly Base File or Folder So I'M Going To Connect to Hdfs and I'M Going To Look for that File Called Second Baseman Dot Csv Now I Can Scratch this Out and Just Say Tempo Otp and in that Case I Would Look for Everything in that Folder Typically I'M GonNa Look for Everything in the Folder because Then I Can Have Processes That Will Create New Files Regularly and I Don't Have To Update My Table Statement
17. 54:07It Is Going To Go through the Entire Data Set It Is Going To Try To Process Everything You'Re GonNa See Things and Then It Will Give You an Error Message It Says Oh We'Ve Hit the Threshold Sorry Something Went Wrong Let's Just Discard all of these Results That Have Been Piling Up on Your Screen so that's Kind Of Disappointing because Typically the Error Message Does Not Tell You Why the Values Were Bad It Usually Just Says Well Good Luck with that You Got a Hundred Billion Row File You Can Use Grep or Something Right Okay so We'Ve Got that Table We'Ve Created the Table and I'M GonNa Show You Here That I Already Have It in Place in
18. 55:29I'M Going To Very Quickly Compare the Sequel Server Version Using Poly Base to the Sequel Server Version Using Linked Server Query I'M Going To Turn on Actual Execution Plans and I'M Going To Run these While I'M Running this Let Me Note that this Query Is Just a Rewriting of the Linked Server Query That I Showed You and What I'M Trying To Get at with this Rewrite Is Trying To Tell the Optimizer That You Really Want To Push Down on Second Baseman You Really Want To Filter To Include Just 50 Records You Really Want To Do As Much as You Can against the Remote Resource and Then I Do the Same Thing Down Here with Dbo that Second Baseman
19. 58:43Actually I Have a Whole Talk on that There's a Link to It in My Slides So Wrapping Everything up Plenty of Ways To Integrate Hadoop with Sequel Server I Recommend that You Mix and Match However Suits Your Needs Figure Out What Works Best for You and Go with It if You Want To Grab the Slides the Demos the Code Links to Additional Resources There at Cs More Dot Info Slash on Slash Hadoop Sql if You Have any Questions At All Please Feel Free To Reach Out to Me My Email Address and Twitter Twitter Handle Are Up Here and I'M GonNa Be Here all Week

" related talks

• 59mHadoop Essentials for the SQL Server ProfessionalBill Preachuk · 24 Hours of PASS: Summit Preview 2015
• 01h 13mIntegrating SQL Server Analysis Services with HadoopEdwin Sarmiento · 24 Hours of PASS: Summit Preview 2015
• 51mData tiering using data Virtualization in SQLAjay Jagannathan · SQLBits 2024
• 51mData tiering using data Virtualization in SQLAjay Jagannathan · SQLBits 2022
• 50mSQL 2022 and S3 – Dell’s overview of consuming object data via Microsoft SQLRobert Sonders, Stephen McMaster · SQLBits 2022
• 23mHadoop & Cloud Storage: Object Store Integration in ProductionRajesh Balamohan · The Fifth Elephant 2016
• 50mChallenges that everyone struggles while productionizing Apache Spark workloadsChetan Khatri · ScalaIO 2019
• 52mPower BI + Azure Synapse Analytics. All you need to start creating a unified analytics solutionPawel Potasinski · SQLBits 2023