For Loop in R
In a real time, we will be in a position to handle the logic using loops. Loop concept is available in all programming language and also in R.
For example, if we want to generate first 200 prime numbers, we have to use loop to get the result.
In R, “for loop” is used to iterate over a vector.
Syntax
for (val in sequence) {
statement
}
In the above syntax, sequence is a vector which we can define and assign into an object and can use the object or we can directly place the vector with the expression. “val” will take the value of sequence and process in a loop. The statement will be triggered until the length of the sequence is completed.
Example,
x <- c(1,2,3,4,5,6,7,8,9,10)
count <- 0
for (val in x) {
if(val %% 2 == 0) count = count+1
}
print(count)
Using Next
We can use the word next inside the for loop and it will change the flow of the process. Check the below example,
for (i in 1:10) {
if (!i %% 2){
}
print(i)
}
If you run above script, I will get the result as 1,2,3,4,5,6,7,8,9,10 (All the values from the 1:10 sequence).
If you want to display only the odd number for the same sequence input then I can just add a “next” keyword inside if loop.
for (i in 1:10) {
if (!i %% 2){
next
}
print(i)
}
First “i” value 1 will go to if condition once the value reaches the keyword “next”. It will redirect to for loop again and it will come again for the second number i.e. 2 and it will continue up to 10.
If and Else function in R
I have discussed that how to use if and else statement in R in my previous article.
In a real time coding, we will be expecting a short form of code which should give the same expected result.
Vectors are basic building block of R Programming and will be used as input.
To apply the same if and else logic for the vector input, we can use if and else function which will give the same result but as a output vector.
Syntax:
Ifelse(test_expression,x,y)
The output of the function could be a vector. In the above syntax, test_expression is a condition which we can apply into the input vector and x is nothing but a custom value or expression, in logical it is called TRUE. If the condition is satisfied then x (TRUE) will display, you can replace x with any value. Here y is FALSE, if the test_expression is not satisfied then y will display.
Example,
number = c(3,5,7,10)
ifelse(number %% 2 == 0,”even”,”odd”)
Logically the result would be FALSE, FALSE, FALSE, TRUE.
If and Else statement in R
As we know, decision making is an important part for every programming. Almost all the projects make use of this decision making logic and sometimes this is very mandatory as well. Considering this importance, R language has if..else statement.
Probably we are in situation to run the set of codes when the condition is match. In that case, we can use if statement. It is very easy to use in R as like other programming language.
The General flow of “IF” statement,
Syntax:
if (test_expression) {
statement
}
Examples:
Example 1
x <- 10
if(x > 0){
print(“Positive number”)
}
Example 2
X<-weekdays(Sys.Date())
if(X == “Monday”)
{ print(“Today is Monday”)}
But in a real time scenario, sometimes we need to run the set of codes when the condition is not match. If that is the case then we need to use if and else statement.
Syntax:
if (test_expression) {
statement1
} else {
statement2
}
Examples:
Example 1
x <- -1
if(x > 0){
print(“Positive Number”)
} else {
print(“Negative number”)
}
Example 2
X<-weekdays(Sys.Date())
if(X == “Monday”)
{ print(“Today is Monday”)} else
{ print (“Today is not Monday”)}
We can use Nested if and else statement,
Syntax:
if ( test_expression1) {
statement1
} else if ( test_expression2) {
statement2
} else if ( test_expression3) {
statement3
} else
statement4
Examples:
Example 1
x <- 0
if (x < 0) {
print(“Negative number”)
} else if (x > 0) {
print(“Positive number”)
} else
print(“Zero”)
Example 2
X<-weekdays(Sys.Date())
if(X == “Monday”)
{ print(“Today is Monday”)} else if (X==”Tuesday”)
{ print (“Today is Tuesday”)} else if (X==”Wednesday”)
{ print (“Today is Wednesay”)} else if (X==”Thursday”)
{ print (“Today is Thursday”)} else if (X==”Friday”)
{ print (“Today is Friday”)} else if (X==”Saturday”)
{ print (“Today is Saturday”)} else
{print (“Today is Sunday”)}
Azure PowerShell Commandlets Issues
As usual, I was trying to use PowerShell Command line tool to manage my azure services. I used to prefer PowerShell as we have more control on it. This time, I got an error message, I was not able to run any Azure commandlets but I was able to add my azure account.
The error message is bit tricky and to solve the issue, I just follow closed and re-opened but still I got the same error message. I found that I got something issue with my saved credentials (token).
Error message,
Get-AzureSubscription : Failed to refresh token. AADSTS90002 : No Service namespace named ‘ ‘ was not found in the data store.
Trace ID:XXXXXXXXXX
Correlation ID: XXXXXXXXXXXXXXXXXX
TimeStamp: 2015-09-12 13:24:477 : The remote server returned an error : (400) Bad Request.
I followed the below steps to solve the issue,
Step 1: Explored the following folder in your system, C:\Users\{User Name}\AppData\Roaming\Windows Azure Powershell.
Note: AppData folder will be in hidden by default and replace {User Name} into your system name.
Step 2: I saw the below files inside the folder and Just deleted all the files.
Step 3: Closed the PowerShell tool and reopen again.
Step 4: Added my azure account, this time, I was able to run all commands successfully.
New feature in Azure Storage
As we know, Microsoft Azure team keep updating the features for all the services. The one of the latest and noticeable update in Azure Storage service is “Account kind”.
This new feature introduced new interesting capabilities which will help customers to decide the right storage access tier.
Before “Account kind”, we used to create a storage account by just specifying the values for Name, Deployment Model, Performance & Location, of course, resource name. There was no data category kind of structure. In this latest update, we can have same the previous feature or we can categories the storage account like Hot or Cool.
The available options under “Account kind”,
1. General Purpose
2. Blob Storage
When we select “Blob Storage”, it will add a new option called “Access tier” which has “Cool” & “Hot” (default).
We can categories our data like “High access data” & “Low access data”. It depends on how frequently, we accessing the data. It has some advantages as well.
If you choose “Cool”, it has lower storage costs and higher access and transaction costs. If “Hot”, it has higher storage costs, lower access and transaction costs.
For more information, please check the Microsoft Azure Documentation here.
Try SQL Server 2016 Release Candidate 3 in Azure Virtual Machine today
Don’t waste your monthly credits and if you have some you can always spin it up with Azure virtual machines to try the SQL Server 2016 RC3 Evaluation on Windows Server 2012 R2.
This is a great and quick fun as you don’t need to manually download the ISO file and keep installing them like I mentioned in this article with step by step instructions.
As you can see in the below Azure portal screen, I am using New => Virtual Machine and then clicked on “See all”, to see all available options and also to search on what I need to search for. The prebuilt images are already available for all of us to try and you can see the highlights that I have made.
Then I got the below screen to confirm on what I was going to create and enable me to create it either with Resource Manager or Classis Deployment Model. In newer version Resource Manager is chosen as default for all deployment models.
In below screen I have given basic options with Name, username, my subscription, resource group, location. Once you have chosen all your settings till 5, then you will get your fully functional SQL Server 2016 RC 3 virtual machine in 10 to 15 minutes. How cool is that.
Step by step instruction on installation of SQL Server 2016 RC3
Following the download of my ISO file from my earlier article, I am going to show you the step by step instructions on installation of SQL Server RC3.
Step 1 is to run the exe file and you get to the Planning tab. Below screenshot, shows the options available in next Installation tab. You can then click on first option which is “New SQL Server stand-alone installation or add features to an existing installation”.
Step 2: Here you have option to evaluate the free edition or enter the product key that you purchased already. Here I chose the Evaluation which is the default screen and then I clicked on next.
Step 3: In the License Terms screen, you have full terms that you can read through to accept the license from Microsoft before start installing.
Step 4: I got error message in the product updates section, as I did not had internet connection in my virtual machine as I had problems in bridging the connection during this setup process. You can click on “Check again” button if you have got the same error screen and still have the internet connection. Then you can click on Next or you can do the product updates later and you can directly click on Next button.
Step 5: Then the system starts installing the necessary setup files and rules. In my below screen you can see the warning messages that I got. As long as it is not failing, we are good to go. So click on Next.
Step 6: Here you will get the options to choose the features. As I am doing main testing on R, SSRS, Polybase, Analysis services, I have ensured to check them all here. You can check/uncheck based on what you need to install.
Step 7: Here I got one task failed for not having Oracle JRE 7 Update 51 to support the polybase installation. After installing this seperately, I was able to continue my installation. Note: till this setup is not installed, the Next button won’t be enabled. Once I have this installation done, I made sure to click on “Re-run” button to test the latest and then this enables me to click on Next button. Here, no need to cancel and then no need to go through all above 6 steps.
Step 8: Then I get the option to configure the name of the instance and I chose it to reflect the RC3 here.
Step 9: As this is the setup in my standalone laptop, I chose the SQL Server as standalone polybase enabled instance.
Step 10: In Server configuration section, it shows all the relevant account names that will be created and the type of startup services (Manual or Automatic). When it is set to automatic, this will then start the services whenever you system is booted. If it is Manual, then you have to start it manually via the services or configuration manager.
Step 11: In the database engine configuration, I have chosen to opt for Mixed Mode to keyin my preferable password, and this also helps me to recover the instance if my current user gone cranky. Also I have added the current user YSMUser1 from the “Add Current User” button. I also have option to choose the data directories, tempdb and filestream.
In here, you also have the new feature of trying to have more than one tempdb and you can get it configured in this place.
Step 12: In Analysis Services Configuration tab, I have chosen to use the Multidimensional and Data Mining server mode and also added the current user to get admin rights.
Step 13: In reporting services configuration, I then have option to choose whether to install and configure or just install only. You still can configure at the later stage, however in regards to Reporting services, I always choose to get it configured as well as you can see below.
Step 14: For the distributed replay controller, I again choose my current user to give the admin permissions to manage it.
Step 15: Then you get to the place where you have option to choose where your controller name, working and result directories should be placed. I have not made any changes here and left it to reflect the default installation folder.
Step 16: This is very new specifically for SQL Server 2016 and you have to accept to download Microsoft R Open and install and also to agree to accept patches and updates for the software. This feature enables us to work on R side from Microsoft itself.
Step 17: I then get this screen to choose the installation path for the Microsoft R Open and Microsoft R Server.
Step 18: I have provided below install path for Microsoft R Open and Microsoft R Server to be installed.
Step 19: This is the last part where you can see the summary of installation on what necessary information you have provided to verify before installation. You still have option to go back to any configuration and make changes and then click on Next.
Step 20: This is the final screen where it says the installation was successful and advise to restart to complete the installation process.
Have good findings in your SQL Server 2016 journey.
R Plots in SQL Server 2016
As we know, Microsoft related the latest version of SQL Server 2016 RC3, download it here. From RC2, we no need to install R components separately. Actually, R database and standalone server both are installed as part of SQL Server 2016 installation itself.
It makes the R integration with SQL Server 2016 is easy.
As part of the integration, we just need to enable the external scripts and start using the R scripts inside SQL Server 2016.
Creating a plot in R console is easy, just write a code for plot and the plot will be displayed in the new window. In SQL Server 2016, we need to store the R script result in VARBINARY and then need to convert to an image file in reporting or using BCP command line tool.
I have provided the steps to get the image (plot) from R scripts in SQL Server 2016.
Solution 1:
Step 1: Create a sample database and table. Insert some values into the table.
Step 2: Do the modification on the table name and column name and run it.
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet;
image_file = tempfile()
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Values);
dev.off();
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
‘
,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));
Step 3: Make sure that you are getting an varbinary result as a output.
Step 4: Wrap the above code within stored procedure, like below and check the result again.
CREATE PROCEDURE Sp_Test1
As
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet;
image_file = tempfile()
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Values); #column name
dev.off();
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
‘
,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));
Step 5: Use below bcp command and convert the Varbinay into jpeg format.
bcp “exec Sp_Test1” queryout “Test.jpg” -S <server name> -d <database name> -U <user name> -P <password>
Step 6: Verify the plot in a above image.
Solution 2:
Step 1: You can create a table and insert this varbinary result and can use in SSRS report with image option.
Step 2: Verify the plot.
Solution 3:
We can export the result as an image file without using bcp tool itself.
Step 1: Do the following changes in the script. Replace the tempfile() with the exact path where you want to store the plot image.
Step 2: Run the script and check the above specified location. Image file will be available with plot.
