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.
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.
Azure–Billing Alert Service
The most expected feature in Azure, available for preview.
Yes, we have been expected an alert for azure credit usage. It is available now. It is in the preview so it will not be available by default, you have to activate it by going through “Preview Feature”
Follow the below steps to activate it.
Step 1: Login with your azure subscription email, and visit the following page.
https://account.windowsazure.com/Subscriptions
Step 2: Look for “preview features” option on the top and select it.
Step 3: Search for “Billing Alert Service”, out of all the listed preview features. Click “try it now” link and this will make you in the queue. The Azure team will look for your request and will activate it for you. It may take at least one week to get activated.
Once this feature is active, you can able to set up an alert for your usage as like below.
The option will look like below,
We can create up to 5 alerts and at present only two alert types are available, “Billing Total” & “Monetary Credits”
Billing Total – An alert is sent when subscription spending exceeds the threshold.
Monetary Credit – An alert is sent when monetary credits drop below the limit.
Manage Azure Storage Account using Azure CLI
We can manage storage account through Azure Portal or PowerShell Scripts.
This article will explain you the commands that you need to use inside Azure CLI to manage storage account.
Download the Azure CLI in following link and choose respective installer package.
Install it on your machine. if you are working with Azure and if you had installed different tools to interact with Azure, you may have different command line tools in your machine. To find the recently installed one, just search for “Microsoft Azure Command Prompt”.
You will see a tool like below,
#First login into Azure using below command
azure login
You need to open the following link in your browser, https://aka.ms/devicelogin and provide the given code to authenticate.
If you have multiple subscription, then you can able to choose the default one.
#List out all the subscriptions
azure account list
#To set the default subscription
azure account set “name of your subscription”
#Create a storage account on the selected subscription
azure storage account create <storage account name>
Above script will ask you to provide two values, “Account Type” & “Location”. Just provide the numbers of the respective information.
To create a containers or all other storage services, we need to fix the created storage account details in environment variable so that when we run a further commands, it will consider the correct storage account. If you missed to set up environment variable then you will end up with following error.
Azure has command called “export” which is used to hold your storage connection string, just like a variable but it is not working as expected. If you try like below,
export AZURE_STORAGE_ACCOUNT=<account_name>
You will get below error. So we can follow different approach to have environment variable.
‘export’ is not recognized as an internal or external command,
operable program or batch file.
#Get the connection string of storage account
azure storage account connectionstring show <storage account name>
#Set Azure Connection string as environment Variable
SET AZURE_STORAGE_CONNECTION_STRING=<connection string>
#Create a container
azure storage container create <container name>
#Upload the blobs
azure storage blob upload <Path> <container name> <blob name>
Example:
azure storage blob upload E:\Encryption.png logs myblob
#Download the blobs
azure storage blob download <container name> <blob name> <local path>
Example:
azure storage blob download logs myblob E:\log.png
#Delete blobs
azure storage blob delete <container name> <blob name>
Example:
azure storage blob delete logs myblog
SQL Server 2016 RC2–Enhancements in R Integration
Microsoft released the latest version of SQL Server 2016 RC2 which has many enhancements. This article exploring the enhancements related R integration in SQL Server.
Before RC2, we need to install SQL Server 2016 prior versions and then R related components separately. This scenario has been changed, We can install R services or standalone R server through SQL Server 2016 RC2 installation itself.
Look at the below changes in the installation center.
New feature to create a standalone R Server.
Confirmation to install Microsoft R Open.
