Matthew McGiffen shares some tips on running SQL Server in Amazon RDS:
Or you can go with Amazon RDS (Relational Database Service). This is more of a managed service where Amazon looks after some aspects of your database server for you. In return you give up some of the control you would have with your own server or VM. You can still pick the version of SQL Server you want installed, usually down to which cumulative update you want – though note that RDS normally lags behind the latest box version of SQL by 3 months or so. RDS is what’s known as a PaaS offering (Platform as a Service).
So, what do you give up and what do you gain? Here’s a quick summary of a few things I’ve noticed. This is not intended to be comprehensive and please bear in mind that AWS is a fast-moving beast – changes happen regularly.
There are some good tips here, so check them out.
Steve Jones elaborates on the default frame that a window function has:
There is a framing clause that I can use after the ORDER BY in the OVER clause. The default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW. At least, this is what appears when you include an ORDER BY clause. Many of us do this, but still get confused with the LAST_VALUE() and FIRST_VALUE functions.
What I really want is a complete set of data, which is either starting from the current row to the end, or includes all values. If I modify my framing clause, I’ll get what I expect.
Click through for a demonstration.
Itzik Ben-Gan has a T-SQL puzzle for us:
As you can see, both T1 and T2 have a numeric column (INT type in this example) called val. The challenge is to match to each row from T1 the row from T2 where the absolute difference between T2.val and T1.val is the lowest. In case of ties (multiple matching rows in T2), match the top row based on val ascending, keycol ascending order. That is, the row with the lowest value in the val column, and if you still have ties, the row with the lowest keycol value. The tiebreaker is used to guarantee determinism.
Click through for the details as well as several solutions.
Eugene Meidinger shares some hard-learned lessons from being self-employed:
I’m naturally an introvert. If you and I have a conversation, it’s like a little taxi meter starts running. I may deeply, deeply enjoy the conversation and find it incredibly exciting, but it still taxes my energy levels. Small talk even more so. Imagine that every time someone chatted about the weather, you had to pay the same price as a Lyft ride to go 4 blocks. That’s how I feel about small talk.
That being said, we are still social creatures, and even introverts need human interaction. Especially so when you need to think through new situations, new problems. One of the things I realized attending PASS Summit is that I need social interaction to thrive. So now I spend a lot more time on Twitter and am part of a peer group of authors. I work down at the library whenever I have the chance.
When I did the work-from-home full-time thing, I sought out user groups to build up some technical skills and, more importantly, to get out of the house and talk to a group of people a couple times a week. That paid off really well in the long run.
Speaking of paying off in the long run, check out Eugene’s BI newsletter.
Tim Mitchell calls out some bad naming practices:
Spaces in object names. The bane of my existence. Most relational databases will allow you to use spaces in object names, requiring anyone accessing that object to put brackets around the name. My rule of thumb is that if it’s something that I will interact with programmatically, it doesn’t get a space in the name. Spaces in object names tend to break things, so please stop doing this.
My favorite naming convention oddity is the idea that if something has “Number” in the name, it’s never a number.
Brent Ozar has a new edition of the Data Professional Salary Survey:
It’s time for our annual salary survey to find out what data professionals make. You fill out the data, we open source the whole thing, and you can analyze the data to spot trends and do a better job of negotiating your own salary:
Take the Data Professional Salary Survey now.
The anonymous survey closes Sunday, January 6, 2019. The results will be completely open source, and shared with the community for your analysis.
I like this survey so much that I delivered a talk at PASS Summit making heavy use of it.
Mark Niemann-Ross explains how you can donate to the R Foundation:
I benefit from the work of the R Foundation. They oversee the language, but also encourage a healthy ecosystem. CRAN happens because of them. Updates to R happen because of them. useR! happens because of them. Every day, you and I are the recipients of some part of their time.
The least we can do is show them some appreciation. If you point your web browser at https://www.r-project.org/foundation/donations.html you’ll find a convenient (and surprisingly inexpensive) place to express your appreciation. As an individual, you can send these kind folks twenty-five euros to tell them you’re in favor of what they do.
But be sure to read the whole thing, especially if you are an American who wants the donation to be tax-deductible. I believe that earmarking in this case is adding special instructions on SIAA’s PayPal page.
John Mount shares some performance measures pitting data.table against various dplyr methods for calculating grouped means:
In this reproduction attempt we see:
– Thedplyrtime being around 0.05 seconds. This is about 5 times slower than claimed.
– Thedplyrsum()/n()time is about 0.2 seconds, about 5 times faster than claimed.
– Thedata.tabletime being around 0.004 seconds. This is about three times as fast as thedplyrclaims, and over ten times as fast as the actual observeddplyrbehavior.
Read the whole thing. If you want to replicate it yourself, check out the RMarkdown file.
Laura Ellis has an updated ggmap tutorial:
For those of you who have been following along with issue #51 in the ggmap repo, you’ll notice that there have been a number of changes in the Google Maps Static API service. Unfortunately these have caused some breakage in previous ggmap functionality.
If you used this package prior to July 2018, you may were likely able to do so without signing up for the Google Static Map API service yourself. As indicated on the the ggmap github repo – “Google has recently changed its API requirements, and ggmap users are now required to provide an API key and enable billing. The billing enablement especially is a bit of a downer, but you can use the free tier without incurring charges. Also, the service being exposed through an easy to use r package that extends ggplot2 is pretty great so I’ll allow it.
This recent API change hurts. But click through for the tutorial, which doesn’t hurt.
Now this works a treat. It waits ten seconds for the SQL instance to come up within the container and then runs the sqlcmd script below.
The problem with this is, it’s a bit of a hack. The HEALTHCHECK command isn’t designed to run once, it’ll carry on running every 10 seconds once the container comes up…not great.
So, what’s the better way of doing it?
Andrew gives us a clear explanation of what’s going on and gives a shout out to Bob Ward’s SQL Server on Linux book.
Kevin Feasel
2018-12-13
Administration, Cloud
No Comment