Skip to content

deepdive-work-with-sql-server-data-using-r.md

Latest commit

 

History

History
122 lines (75 loc) · 7.18 KB

deepdive-work-with-sql-server-data-using-r.md

File metadata and controls

122 lines (75 loc) · 7.18 KB
title Create a database and permissions (SQL and RevoScaleR deep dive)| Microsoft Docs
ms.prod sql
ms.technology machine-learning
ms.date 11/27/2018
ms.topic tutorial
author HeidiSteen
ms.author heidist
manager cgronlun

Lesson 1: Create a database and permissions

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md-winonly]

This article is part of the RevoScaleR tutorial on how to use RevoScaleR functions with SQL Server.

In this lesson, you set up the environment and add the data you need for training your models and run some quick summaries of the data. As part of the process, you must complete these tasks:

  • Create a new database to store the data for training and scoring two R models.

  • Create an account (either a Windows user or SQL login) to use when communicating between your workstation and the [!INCLUDEssNoVersion] computer.

  • Create data sources in R for working with [!INCLUDEssNoVersion] data and database objects.

  • Use the R data source to load data into [!INCLUDEssNoVersion].

  • Use R to get a list of variables and modify the metadata of the [!INCLUDEssNoVersion] table.

  • Create a compute context to enable remote execution of R code.

  • (Optional) Enable tracing on the remote compute context.

Create the database and user

For this walkthrough, create a new database in [!INCLUDEssCurrent], and add a SQL login with permissions to write and read data, and to run R scripts.

Note

If you are only reading data, the account that runs the R scripts requires SELECT permissions (db_datareader role) on the specified database. However, in this tutorial, you must have DDL admin privileges to prepare the database, and to create tables for saving the scoring results.

Additionally, if you are not the database owner, you need the permission, EXECUTE ANY EXTERNAL SCRIPT, in order to execute R scripts.

  1. In [!INCLUDEssManStudioFull], select the instance where [!INCLUDErsql_productname] is enabled, right-click Databases, and select New database.

  2. Type a name for the new database. You can use any name you want; just remember to edit all the [!INCLUDEtsql] scripts and R scripts in this walkthrough accordingly.

    [!TIP] To view the updated database name, right-click Databases and select Refresh .

  3. Click New Query, and change the database context to the master database.

  4. In the new Query window, run the following commands to create the user accounts and assign them to the database used for this tutorial. Be sure to change the database name if needed.

Windows user

 -- Create server user based on Windows account
USE master
GO
CREATE LOGIN [<DOMAIN>\<user_name>] FROM WINDOWS WITH DEFAULT_DATABASE=[DeepDive]

 --Add the new user to tutorial database
USE [DeepDive]
GO
CREATE USER [<user_name>] FOR LOGIN [<DOMAIN>\<user_name>] WITH DEFAULT_SCHEMA=[db_datareader]

SQL login

-- Create new SQL login
USE master
GO
CREATE LOGIN DDUser01 WITH PASSWORD='<type password here>', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

-- Add the new SQL login to tutorial database
USE [DeepDive]
GO
CREATE USER [DDUser01] FOR LOGIN [DDUser01] WITH DEFAULT_SCHEMA=[db_datareader]
  1. To verify that the user has been created, select the new database, expand Security, and expand Users.

Troubleshooting

This section lists some common issues that you might run across in the course of setting up the database.

  • How can I verify database connectivity and check SQL queries?

    Before you run R code using the server, you might want to check that the database can be reached from your R development environment. Both Server Explorer in Visual Studio and SQL Server Management Studio are free tools with powerful database connectivity and management features.

    If you don't want to install additional database management tools, you can create a test connection to the SQL Server instance by using the ODBC Data Source Administrator in Control Panel. If the database is configured correctly and you enter the correct user name and password, you should be able to see the database you just created and select it as your default database.

    If you cannot connect to the database, verify that remote connections are enabled for the server, and that the Named Pipes protocol has been enabled. Additional troubleshooting tips are provided in this article: Troubleshoot Connecting to the SQL Server Database Engine.

  • My table name has datareader prefixed to it - why?

    When you specify the default schema for this user as db_datareader, all tables and other new objects created by this user are prefixed with the schema name. A schema is like a folder that you can add to a database to organize objects. The schema also defines a user's privileges within the database.

    When the schema is associated with one particular user name, the user is the schema owner. When you create an object, you always create it in your own schema, unless you specifically ask it to be created in another schema.

    For example, if you create a table with the name TestData, and your default schema is db_datareader, the table is created with the name <database_name>.db_datareader.TestData.

    For this reason, a database can contain multiple tables with the same names, as long as the tables belong to different schemas.

    If you are looking for a table and do not specify a schema, the database server looks for a schema that you own. Therefore, there is no need to specify the schema name when accessing tables in a schema associated with your login.

  • I don't have DDL privileges. Can I still run the tutorial??

    Yes; however, you should ask someone to pre-load the data into the [!INCLUDEssNoVersion] tables, and skip past the sections that call for creating new tables. The functions that require DDL privileges are called out in the tutorial wherever possible.

    Also, ask your administrator to grant you the permission, EXECUTE ANY EXTERNAL SCRIPT. It is needed for R script execution, whether remote or by using sp_execute_external_script.

Next step

Create SQL Server data objects using RxSqlServerData

Overview

Data Science Deep Dive: Using the RevoScaleR Packages