A Fine Slice Of SQL Server
Anubhav Tarar notes that you can run Hive queries against data even without a Hadoop installation:
Starting with release 0.7, Hive also supports a mode to run map-reduce jobs in local-mode automatically
you just have to do two things first create your warehouse in local system and give the default fs name to local put these properties inside your hive-site.xml
This is a fairly short post; click through to see the changes you’d make to hive-site.xml.
Comments closedSibanjan Das has started a new series on text mining in R:
Next, we need to preprocess the text to convert it into a format that can be processed for extracting information. It is essential to reduce the size of the feature space before analyzing the text. There are various preprocessing methods that we can use here, such as stop word removal, case folding, stemming, lemmatization, and contraction simplification. However, it is not necessary to apply all of the normalization methods to the text. It depends on the data we retrieve and the kind of analysis to be performed.
The series starts off with a quick description of some preprocessing steps and then building an LDA model to extract key terms from articles.
Comments closedI ran into an error after installing SQL Server 2017:
After installation completed, the DBA enabled SQL Server 2017 Machine Learning Services, but as soon as I tried to run a simple R script, it stalled for about 30 seconds and then I got an error:
Msg 39012, Level 16, State 1, Line 0
Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime.
STDERR message(s) from external script:
Error: could not find function “rxSqlUpdateLibPaths”
Execution halted
Click through for the solution.
Comments closedBut what happens if we try to use STRING_SPLIT with a multi-character separator, like this?
SELECT * FROM STRING_SPLIT(@GodawfulString, ‘,,’)
Msg 214, Level 16, State 11, Line 67
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.Ah. That’s a no, then.
Click through for the solution.
Comments closedFrank Gill has a script to find current Query Store context settings:
The following query will return the hex and integer value for each row in the table (NOTE: Query store must be enabled for the database to return values):
USE YourQueryStoreDatabase; SELECT set_options, CONVERT(INT, set_options) AS IntSetOptions FROM sys.query_context_settings;The set_options value represents a bit mask, with each binary digit representing a specific set option. The full list of values can be found here. I created stored procedure ReturnSetOptions to take the IntSetOptions from the query above and return the set options represented. The code for the procedure is listed below.
Read on to get a script which breaks the bitmask field into human-readable results.
Comments closedAdam Bertram shows how to write to a file using Powershell:
Set-Contentis one of those core PowerShell cmdlets that I can’t do without. I still remember using VBscript before we could use PowerShell to write to a file. I remember always trying to remember what kind of object I needed to use and the method name. Was it FileSystemObject, FileObject or what? It was a pain! Also, even when I did recall the method name wasCreateTextFile, I’d always forget to addTrueas the second argument.Here’s an example of the monstrosity I’m talking about.
Click through to see how easy writing is with Powershell.
Comments closedKoen Verbeeck diagnoses an awful Analysis Services error:
This really didn’t make any sense. However, in one of the Discover Begin/End events, the same number appeared again: 8192 (this time explicitly marked as locale identifier). Hmmm, I had problems with weird locales before. I dug into my system, and yes, the English (Belgium) locale was lingering around. I removed it from my system and lo and behold, I could log into SSAS with SSMS again. Morale of the story: if you get weird errors, make sure you have a normal locale on your machine because apparently the SQL Server client tools go bonkers.
Worth reading the whole thing. And also maybe just using en-US for all locales; at least that one gets tested…
Comments closedJeff Schwartz gives us some guidance on finding good candidate indexes for tuning:
Index usage and tuning metrics became available on SQL Server 2005 with Dynamic Management Views and Functions, which will be discussed later. However, the meanings and significance of index DMV/DMF metrics are still not well understood by many despite only minor additions over the years. Specifically, the following list contains a synopsis of the topics that the author has observed to be the most salient index-related issues:
Identifying
- Queries that need an index to function efficiently
- Which indices, if any, are used by a query (and how they are used, e.g., randomly or sequentially)
- Tables (and their indices) that merit evaluation and potential adjustment
- Indices that duplicate functionality of others
Understanding when
- A new index is truly needed and what improvement can be anticipated
- An index can be deleted without harming performance
- An index should be adjusted or added despite the query not producing any missing index warnings
Understanding why having too many indices results in
- Inserts and updates taking too long and/or creating blocking
- Suboptimal query plans being generated because there are too many index choices
Knowing Database Engine Tuning Advisor (DTA) pros & cons
Jeff starts with the basics of indexes, followed by some general strategy. This promises to be the first of several posts on the topic.
Comments closedAnish Sing Walia walks us through creating a dashboard using Shiny:
Shinyis an amazing R package which lets the R developers and users build amazing web apps using R itself. It lets the R users analyze, visualize and deploy their machine learning models directly in the form of the web app. This package lets you host standalone apps on a webpage or embed them in R markdown documents or build dashboards and various forecasting applications. You can also extend your Shiny apps with CSS themes, htmlwidgets, and JavaScript actions.Shinylets us write client-side front-end code in R itself and also lets users write server-side script in R itself. More details on this package can be found here.I recently learned Shiny and started developing a web application using it.And since then I have been in love with it and have been using it in each and every data science and analytics project. The syntax is super easy to understand and there are lots of amazing articles and documentation available for you to learn it and use it. I personally had a background of developing full-stack web applications using HTML, CSS and javascript and other JS based scripting languages so I found the syntax easy.
I keep meaning to learn Shiny and someday I will, just to prove to my intern that she’s not the only one here who can…
1 CommentAlex Yates talks about database rollback in the event of a code release failure:
A thorny topic. Rolling back code is easy. Normally you can just redeploy the old binaries and you’re done. Databases are more difficult – because data. The existence of persistent data has two significant consequences:
You can’t just redeploy the entire database – you need to create a script to migrate your existing database from the existing (undesirable) state back to the old (desired) state. This probably isn’t an automated process.
You need to think about new data that has entered the database since the deployment. Do you want to keep it? Do you need to process it to get it into an older format? (E.g. if you need to undo a column split.)
Alex has some helpful tips for structuring database changes. Me, I just never look back…which is actually one of the strategies Alex talks about.
Comments closed