SQL-On-Hadoop: Hive - Part II (SQLServerCentral 12/11/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [SQL-On-Hadoop: Hive - Part II]( - [Azure SQL Database Virtual Network Service Endpoints and Rules]( - [Generating test data in JSON files using SQL Data Generator]( - [Database Fundamentals #15: Modifying Data With T-SQL]( (From the SQLServerCentral Blogs) - [Automating T-SQL Code Analysis]( (From the SQLServerCentral Blogs) The Voice of the DBA Delayed gratification or not? Today we have a guest editorial as Steve is out of the office. You have probably seen those videos. The ones where they put kids in a room by themselves with a plate and a single cookie on it. They tell them if they will wait five minutes and do not eat the cookie, they will get a second cookie. If they eat the cookie before the five minutes is up, there will be no second cookie. The children have all sorts of responses. Most cannot resist the temptation and eat the cookie and don’t get a second one. A few endure the suffering and get rewarded with a second cookie. I think there can be a fair bit of delayed gratification in our line of work too. Sometimes, when we design a system or setup servers, we can put certain things in place, planning for the future. It takes more time and more effort, but we do it anyway because we think it will pay off in the long run. It could be installing extra features like SSIS or Reporting Services. It could be spending more time on design trying to build things into the schema that we think may be needed in the future. I was faced with this question of delayed gratification recently outside of work. I was playing one of my time wasting games on my phone. I was tempted to buy some gems for a few dollars, so I could get some upgrades. The issue was, if I just waited 10 days and continued to work at the levels, I would get twice as many gems for free. Ten days is a long time. I can buy the smaller amount of gems now and get the upgrades I want now. Which do I choose? Do I delay my gratification and patiently wait? Or do I give in and spend a few dollars on a silly game? I can say from experience that delayed gratification is usually the correct choice. So why is it so hard to choose it each time? Why do we want the easy path and the quick solution? Why do we resist doing the extra work now? Perhaps a more important question is: Does it matter? First, I think it does matter. I think we often know the right thing to do, but try to convince ourselves otherwise. Believe me, the conversations in my head about it is not a big deal, it is just a few dollars, go ahead and buy the gems now. I think sometimes we are trying to convince ourselves that the minimum is enough, that it is okay to take the easy route now. Here is the problem. Regret and remorse. I am sure you have all experienced it. You know you should have put more effort in. Or you should have done this or that and now you and your company are paying the consequences. Or I wasted a few dollars on a game, it just wasn’t worth it. So how about you? What would you do? Would you delay your gratification or not? Share a time you delayed gratification, was it worth it or not? Ben Kubicek from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL Prompt]( Write, format, and refactor SQL effortlessly with SQL Prompt Writing SQL is 50% faster with SQL Prompt. Your SQL code can be formatted just the way you like it, you can create and share snippets with your team, and with tab coloring you’ll never deploy to the wrong environment again. [Download your free trial]( [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( Featured Contents  [] [SQL-On-Hadoop: Hive - Part II]( Frank A. Banin from [SQLServerCentral.com]() Running Interactive and Batch SQL Queries on Hadoop and other distributed clusters using SQL.[More »]( ---------------------------------------------------------------  [] [Azure SQL Database Virtual Network Service Endpoints and Rules]( Additional Articles from [Database Journal]() One of the most commonly requested Azure SQL Database features has been support for more granular control of server-level firewall settings. A recently announced public preview of Azure SQL Database and Data Warehouse VNET service endpoints and rules promises to address this limitation.[More »]( ---------------------------------------------------------------  [] [Generating test data in JSON files using SQL Data Generator]( SQL Data Generator is adept at filling SQL Server databases with ‘spoof’ data, for use during development and testing activities. However, what if instead of a SQL Server database full of fake data, you need a JSON file?[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Database Fundamentals #15: Modifying Data With T-SQL]( Grant Fritchey from [SQLServerCentral Blogs]( The preferred method for modifying your data within a database is T-SQL. While the last Fundamentals post showed how to...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Automating T-SQL Code Analysis]( Grant Fritchey from [SQLServerCentral Blogs]( With all the options available within T-SQL these days, it’s more and more imperative that our code be clear and...[More »]( Question of the Day Today's Question (by Steve Knox): I have the following variable: DECLARE @A VARCHAR(6) = 'ABCDEF' I want to produce the following output: values A B C D E F How can I do this using the STRING_SPLIT() function in SQL 2016+? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 2 points in this category: STRING_SPLIT. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [SQL Practice Problems: 57 beginning, intermediate, and advanced challenges]( It's easy to find basic SQL syntax and keyword information online. What's hard to find is challenging, well-designed, real-world problems—the type of problems that come up all the time when you're dealing with data. Learning how to solve these problems will give you the skill and confidence to step up in your career. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I want to build an advent calendar for Christmas that will count down the days to Christmas, while showing the current date. Which one of these works the best? -- Query 1 WITH myTally (n) AS ( SELECT TOP 25 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM ( VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a (n) CROSS JOIN ( VALUES (1),(2),(3)) AS b (n) ) SELECT Today = DATEADD(DAY, 25 - n, '2017-12-25'), DaysLeft = 25 - n FROM myTally; -- Query 2 GO WITH myTally (n) AS ( SELECT TOP 25 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM ( VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a (n) CROSS JOIN ( VALUES (1),(2),(3)) AS b (n) ) SELECT Today = DATEADD(DAY, n, '2017-12-25'), DaysLeft = 25 - n FROM myTally; GO -- Query 3 WITH myTally (n) AS ( SELECT TOP 25 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM ( VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a (n) CROSS JOIN ( VALUES (1),(2),(3)) AS b (n) ) SELECT Today = DATEADD(DAY, n - 25, '2017-12-25'), DaysLeft = 25 - n FROM myTally; GO -- Query 4 WITH myTally (n) AS ( SELECT TOP 25 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM ( VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a (n) CROSS JOIN ( VALUES (1),(2),(3)) AS b (n) ) SELECT Today = DATEADD(DAY, n - 25, '2017-12-25'), DaysLeft = n FROM myTally; GO Answer: Query 3 Explanation: A advent calendar counts down days, so from Dec 1, we would expect that there are 24 days left. Query 3 will do this, using the rising tally table counter to subtract from the count of 25. --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [joining lots and lots of tables in a vew]( - have got a query(view) that needs to join multiple tables in a view. Currently have over 100 tables in the... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Link SQL Server 2014 to a Progress Database, is it possible?]( - Hello Everyone, i was tasked with a weird question, and sorta told them it is possible, or could be, but, is... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Copying rows from AS400 table into SQL table based on a sequence field.]( - Hello,  I am using SQL Server 2014 [computed columns]( - Hi, With some googling, I learnt that SQL Server doesn't have persisted column names (like Netezza supports, for instance), which demands... [adding 365 partitions to existing scheme and fucntion in sql server 2014]( - Hi My tables are partitioned on business date. My requirement is to add 365 partitions for next year to existing partition... [Running DBCC commands on SQL Azure db]( - Hi All, For tuning poorly performing query on a Testing server or local SQL instance on a Laptopn, we used to... [Changing sys.configurations for SQL Azure instance? Is that possible?]( - Hi All, If I would like to change the sp_configure settings on a SQL Azure instance, how can I do that?... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [How to delete data in child tables first and then parent table data?]( - Hi All, Looking for a TSQL script which can delete/truncate data from child tables first and then parent tables. Does... --------------------------------------------------------------- [SQL Server 2008]( : [T-SQL (SS2K8)]( [Scripting Tables with ALTER with dependencies]( - Dear Experts, Please advise how I can script all the tables of a database with ALTER option . It should script the... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [Can we restore copy-only full backup and regular tlog backup?]( - Good Morning Experts Can we restore copy-only full backup and regular tlog backup? --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Administration]( [Monitor spids on SQL Azure instance? Can we do it?]( - Hi All, Can I do the below for SQL Azure instance/db? Suppose, I want to take a look at what is... --------------------------------------------------------------- [Database Design]( : [Virtualization]( [metrics]( - Is there a good performance metric to build a sql server 2016 instance(s) that has no past history, basically a... --------------------------------------------------------------- [SQL Server 2005]( : [T-SQL (SS2K5)]( [Find and count Incomplete records in a table]( - Hi, I have a problem which is stretching my basic T-SQL coding capabilities and hope you might be able to... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com