Criar objetos de dados SQL Server usando RxSqlServerData (SQL Server e tutorial RevoScaleR)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

Este é o tutorial 2 da série de tutoriais RevoScaleR sobre como usar funções RevoScaleR com o SQL Server.

Este tutorial é uma continuação da criação de bases de dados: adicionar tabelas e carregar dados. Se um DBA criou a base de dados e fez login no tutorial dois, podes adicionar tabelas usando um IDE R como o RStudio ou uma ferramenta integrada como o Rgui.

A partir do R, ligue-se ao SQL Server e use as funções do RevoScaleR para realizar as seguintes tarefas:

  • Criar tabelas para dados de treino e previsões
  • Carregar tabelas com dados de um ficheiro de .csv local

Os dados de exemplo são dados simulados de fraude com cartão de crédito (o conjunto de dados ccFraud), particionados em conjuntos de dados de treino e pontuação. O ficheiro de dados está incluído no RevoScaleR.

Usa um IDE R ou Rgui para completar estas tarefas. Certifique-se de usar os executáveis R encontrados neste local: C:\Program Files\Microsoft\R Client\R_SERVER\bin\x64 (either Rgui.exe se estiver a usar essa ferramenta, ou um IDE R apontando para C:\Program Files\Microsoft\R Client\R_SERVER). Ter uma estação de trabalho cliente R com estes executáveis é considerado um pré-requisito deste tutorial.

Crie a tabela de dados de treino

  1. Armazene a cadeia de ligação da base de dados numa variável R. Abaixo estão dois exemplos de cadeias válidas de ligação ODBC para SQL Server: uma usando login SQL e outra para autenticação integrada no Windows.

    Certifique-se de modificar o nome do servidor, nome de utilizador e palavra-passe conforme apropriado.

    Login SQL

    sqlConnString <- "Driver=SQL Server;Server=<server-name>; Database=RevoDeepDive;Uid=<user_name>;Pwd=<password>"

    Autenticação do Windows

    sqlConnString <- "Driver=SQL Server;Server=<server-name>;Database=RevoDeepDive;Trusted_Connection=True"

  2. Especifique o nome da tabela que quer criar e guarde-a numa variável R.

    sqlFraudTable <- "ccFraudSmall"

    Como a instância do servidor e o nome da base de dados já estão especificados como parte da cadeia de ligação, quando se combinam as duas variáveis, o nome totalmente qualificado da nova tabela torna-se instance.database.schema.ccFraudSmall.

  3. Opcionalmente, especifique rowsPerRead para controlar quantas linhas de dados são lidas em cada lote.

    sqlRowsPerRead = 5000

    Embora este parâmetro seja opcional, defini-lo pode resultar em cálculos mais eficientes. A maioria das funções analíticas melhoradas no RevoScaleR e MicrosoftML processa os dados em blocos. O parâmetro rowsPerRead determina o número de linhas em cada bloco.

    Pode ser necessário experimentar esta definição para encontrar o equilíbrio certo. Se o valor for demasiado grande, o acesso aos dados pode ser lento se não houver memória suficiente para processar dados em blocos desse tamanho. Por outro lado, em alguns sistemas, se o valor de rowsPerRead for demasiado pequeno, o desempenho também pode abrandar.

    Como valor inicial, use o tamanho padrão do processo em lote definido pela instância do motor de base de dados para controlar o número de linhas em cada bloco (5.000 linhas). Guarda esse valor na variável sqlRowsPerRead.

  4. Defina uma variável para o novo objeto fonte de dados e passe os argumentos previamente definidos ao construtor RxSqlServerData . Note que isto apenas cria o objeto fonte de dados e não o preenche. Carregar dados é uma etapa separada.

    sqlFraudDS <- RxSqlServerData(connectionString = sqlConnString,
   table = sqlFraudTable,
   rowsPerRead = sqlRowsPerRead)

Criar a tabela de dados de pontuação

Seguindo os mesmos passos, crie a tabela que contém os dados de pontuação usando o mesmo processo.

  1. Crie uma nova variável R, sqlScoreTable, para armazenar o nome da tabela usada para a pontuação.

    sqlScoreTable <- "ccFraudScoreSmall"

  2. Forneça essa variável como argumento à função RxSqlServerData para definir um segundo objeto fonte de dados, sqlScoreDS.

    sqlScoreDS <- RxSqlServerData(connectionString = sqlConnString,
   table = sqlScoreTable, rowsPerRead = sqlRowsPerRead)

Como já definiu a cadeia de ligação e outros parâmetros como variáveis no espaço de trabalho R, pode reutilizá-los para novas fontes de dados que representam diferentes tabelas, vistas ou consultas.

Note

A função utiliza argumentos diferentes para definir uma fonte de dados baseada numa tabela inteira do que para uma fonte de dados baseada numa consulta. Isto deve-se ao facto de o motor de base de dados do SQL Server ter de preparar as consultas de forma diferente. Mais tarde neste tutorial, aprende a criar um objeto fonte de dados baseado numa consulta SQL.

Carregar dados em tabelas SQL usando R

Agora que criou as tabelas do SQL Server, pode carregar dados nelas usando a função Rx apropriada.

O pacote RevoScaleR contém funções específicas para tipos de fonte de dados. Para dados de texto, use o RxTextData para gerar o objeto fonte de dados. Existem funções adicionais para criar objetos fonte de dados a partir de dados Hadoop, dados ODBC, e assim por diante.

Note

Nesta secção, deve ter permissões para executar DDL na base de dados.

Carregar dados na tabela de treino

  1. Crie uma variável R, ccFraudCsv, e atribua à variável o caminho do ficheiro CSV que contém os dados da amostra. Este conjunto de dados é fornecido no RevoScaleR. O "sampleDataDir" é uma palavra-chave na função rxGetOption .

    ccFraudCsv <- file.path(rxGetOption("sampleDataDir"), "ccFraudSmall.csv")

    Repare na chamada para rxGetOption, que é o método GET associado a rxOptions no RevoScaleR. Use esta utilidade para definir e listar opções relacionadas com contextos de computação locais e remotos, como o diretório partilhado padrão ou o número de processadores (núcleos) a usar nos cálculos.

    Esta chamada em particular obtém os exemplos da biblioteca correta, independentemente de onde esteja a executar o seu código. Por exemplo, tenta executar a função no SQL Server e no teu computador de desenvolvimento, e vê como os caminhos diferem.

  2. Defina uma variável para armazenar os novos dados e use a função RxTextData para especificar a fonte de dados de texto.

    inTextData <- RxTextData(file = ccFraudCsv,      colClasses = c(
    "custID" = "integer", "gender" = "integer", "state" = "integer",
    "cardholder" = "integer", "balance" = "integer",
    "numTrans" = "integer",
    "numIntlTrans" = "integer", "creditLine" = "integer",
    "fraudRisk" = "integer"))

    O argumento colClasses é importante. Usa-se para indicar o tipo de dado a atribuir a cada coluna de dados carregada a partir do ficheiro de texto. Neste exemplo, todas as colunas são tratadas como texto, exceto as colunas nomeadas, que são tratadas como inteiros.

  3. Neste momento, talvez queira fazer uma pausa e visualizar a sua base de dados no SQL Server Management Studio. Atualize a lista de tabelas na base de dados.

    Pode ver que, embora os objetos de dados R tenham sido criados no seu espaço de trabalho local, as tabelas não foram criadas na base de dados do SQL Server. Além disso, nenhum dado foi carregado do ficheiro de texto para a variável R.

  4. Insira os dados chamando a função rxDataStep.

    rxDataStep(inData = inTextData, outFile = sqlFraudDS, overwrite = TRUE)

    Assumindo que não há problemas com a sua cadeia de ligação, após uma breve pausa, deverá ver resultados como estes:

    Total de linhas escritas: 10000, Tempo total: 0,466Linhas Legidas: 10000, Total de Linhas Processadas: 10000, Tempo Total de Chunk: 0,577 segundos

  5. Atualize a lista de tabelas. Para verificar se cada variável tem os tipos de dados corretos e foi importada com sucesso, pode também clicar com o botão direito na tabela no SQL Server Management Studio e selecionar Selecionar Top 1000 Linhas.

Carregar dados na tabela de pontuação

  1. Repita os passos para carregar o conjunto de dados usado para a pontuação na base de dados.

    Comece por fornecer o caminho para o ficheiro de origem.

    ccScoreCsv <- file.path(rxGetOption("sampleDataDir"), "ccFraudScoreSmall.csv")

  2. Use a função RxTextData para obter os dados e guardá-los na variável inTextData.

    inTextData <- RxTextData(file = ccScoreCsv,      colClasses = c(
    "custID" = "integer", "gender" = "integer", "state" = "integer",
    "cardholder" = "integer", "balance" = "integer",
    "numTrans" = "integer",
    "numIntlTrans" = "integer", "creditLine" = "integer"))

  3. Chame a função rxDataStep para sobrescrever a tabela atual com o novo esquema e dados.

    rxDataStep(inData = inTextData, sqlScoreDS, overwrite = TRUE)

    • O argumento inData define a fonte de dados a utilizar.

    • O argumento outFile especifica a tabela no SQL Server onde se quer guardar os dados.

    • Se a tabela já existir e não usares a opção de sobrescrever , os resultados são inseridos sem truncamento.

Mais uma vez, se a ligação foi bem-sucedida, deverá ver uma mensagem a indicar a conclusão e o tempo necessário para escrever os dados na tabela:

Total de linhas escritas: 10000, Tempo total: 0,384Linhas Legidas: 10000, Total de Linhas Processadas: 10000, Tempo Total de Chunk: 0,456 segundos

Mais sobre o rxDataStep

rxDataStep é uma função poderosa que pode realizar múltiplas transformações numa trama de dados R. Também pode usar o rxDataStep para converter dados na representação exigida pelo destino: neste caso, SQL Server.

Opcionalmente, pode especificar transformações nos dados, usando funções R nos argumentos para rxDataStep. Exemplos destas operações são fornecidos mais adiante neste tutorial.

Passo seguinte

Consultar e modificar os dados do SQL Server

  • Last updated on