Introdução ao SQL Server R Services 2016

Fala galera, uma das novidades do SQL Server 2016 é a possibilidade de executar scripts da linguagem R internamente na plataforma de dados da Microsoft. Isso avança mais um passo dentro da abordagem deles para a área de Big Data e Data Science, que já possui coisas como o HDInsight, PowerBI, Datazen, Data Lake e outros. Aqui vou mostrar como instalar os pré-requisitos e configurar o R dentro do CTP3 – Community Technology
Preview – do SQL Server 2016. Pode ser que isso mude até a versão final, mas até lançarem, dá pra brincar com o R seguindo estes passos…

 

Pré-Requisitos

SQL Server 2016 CTP 3 (2.7GB)

Revolution R Open 3.2.2 for Revolution R Enterprise 7.5.0 (60MB)

Revolution R Enterprise 7.5.0 (RRE-7.5.0) (470MB)

Instalação

O primeiro passo é instalar o SQL Server 2016 CTP 3, lembrando de habilitar o recurso de Advanced Analytics Extensions.

Depois de concluir a instalação, é necessário instalar o Revolution R Open 3.2.2 for Revolution R Enterprise 7.5.0 e em seguida Revolution R Enterprise 7.5. Estas ferramentas são, respectivamente, a distribuição do R com o runtime e algumas bibliotecas, e, um grupo de melhorias nos pacotes que oferece suporte de alta performance á diversas origens de dados.


Da parte de instalação é só isso, agora faltam só mais alguns passos de configuração e o R começará a responder de dentro do SQL Server.

Configuração

Pode parecer complexo pra quem vê a primeira vez, mas é bem simples. A primeira coisa é habilitar a execução de scripts externos no SQL Server que por padrão, vem desabilitado. Você pode verificar isso chamando o código SP_CONFIGURE.

Para habilitar, execute o código abaixo:

Exec sp_configure 'external scripts enabled', 1;
reconfigure;

O resultado deve ter mudado a linha do External Scripts Enabled, que agora está habilitado.

Depois de habilitar a execução de scripts externos no SQL Server, é necessário registrar um daqueles serviços externos que foram instalados, é o serviço do Revolution R Open. Para isso, abra um prompt de comando com privilégios de admin e vé até o caminho que fez a instalação do RRO. No meu caso foi neste caminho (provavelmente você precisará editar isso para que funcione no seu servidor).

c:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\
library\RevoScaleR\rxLibs\x64>

Procure um executável chamado registerRext.exe e passe a instrução de instalar. Como eu renomeei a minha instância para SQL2016, eu também precisei passar o nome da instância para o executavel. Ficou assim:

c:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\
library\RevoScaleR\rxLibs\x64>registerRext.exe /install /instance:SQL2016

 

Ele executa um monte de instalações e registros internos, olha o resultado do prompt de comando depois dos registros:

Source directory to pick the RExtension binaries determined to be “c:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\”.

Connecting to SQL server…

Sql server binn directory is “C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Binn”.

Sql server log directory is “C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log”.

Creating user account pool…

Creating user accounts.

Processing 20/20

Adding firewall rule for user account pool.

***WARNING: For security reasons, it is recommended to block network access for

R processes executing under local user accounts. It appears that Windows Firewall is currently disabled for the Network Profiles DOMAIN, PRIVATE. Please enable Windows Firewall or use another firewall to block network access

Creating working directory for user accounts.

Saving user account configuration.

User account pool with 20 users created.

R installation found at C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2.MPI installation found at C:\Program Files\Microsoft MPI.

Settings file C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Binn\rlauncher.config created.

Stopping service MSSQLLaunchpad$SQL2016…

Stopping service MSSQL$SQL2016…

Copied xp_callrre.dll from c:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\ to C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Binn.

Copied RLauncher.dll from c:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\ to C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Binn.

Starting service MSSQLLaunchpad$SQL2016…

Connecting to SQL server…

Created role db_rrerole

Added extended stored procedure xp_ScaleR_init_job.

Granted Execute for extended stored procedure xp_ScaleR_init_job to db_rrerole.

Added extended stored procedure xp_ScaleR_queue_job.

Granted Execute for extended stored procedure xp_ScaleR_queue_job to db_rrerole.

Added extended stored procedure xp_ScaleR_retrieve_results.

Granted Execute for extended stored procedure xp_ScaleR_retrieve_results to db_rrerole.

Added extended stored procedure xp_ScaleR_query_status.

Granted Execute for extended stored procedure xp_ScaleR_query_status to db_rrerole.

Added extended stored procedure xp_ScaleR_cancel_job.

Granted Execute for extended stored procedure xp_ScaleR_cancel_job to db_rrerole.

Added extended stored procedure xp_ScaleR_cleanup.

Granted Execute for extended stored procedure xp_ScaleR_cleanup to db_rrerole.

R extensibility installed successfully.

Agora só falta reiniciar o serviço do SQL Server.

Primeira execução!

Agora é momento de abrir o SQL Server 2016 e executar o seu código em R. Peguei um código já criado aqui no blog anteriormente (Trabalhando com cores no R), e reaproveitei. Execute o código abaixo:

EXECUTE   sp_execute_external_script
                @language = N'R'
              , @script = N'OutputDataSet <- InputDataSet
              color <- colorRampPalette(c("red","yellow", "green"))
              color(10)
              a <- c(1:10)
              jpeg("C:/TEMP/Barras.jpg")
              barplot(a, col=color(10), main="R no SQL Server 2016")
              dev.off()'
              , @input_data_1 = N'SELECT ''R no SQL Server 2016'' as Col'

              WITH RESULT SETS ((col varchar(30) not null));
go

 

E vá até sua pasta C:\Temp e pocure o arquivo Barras.jpg. Se tudo deu certo, você terá um gráfico como este abaixo gerado pelo R diretamente do seu SQL Server 2016.

Agora é explorar as funcionalidades e possibilidades do R, e integrar isso ao seu ambiente. Fique atento que mais posts sobre R dentro do SQL Server serão publicados em breve!

Links de Referência

Todas estas configurações acima foram consolidadas e tiradas destes links de referências abaixo.

Installing SQL Server R Services

Install Advanced Analytics Extensions

Install R Packages and Providers for SQL Server R Services

Configure and Manage Advanced Analytics Extensions

Compartilhe o post:
RSS
Follow by Email
Facebook
YOUTUBE
YOUTUBE
LinkedIn

Comentários

comments

86 thoughts on “Introdução ao SQL Server R Services 2016

  1. I must show my appreciation to the writer just for bailing me out of this type of challenge. As a result of scouting through the world wide web and seeing strategies that were not helpful, I believed my life was gone. Being alive without the approaches to the problems you’ve solved through this post is a crucial case, as well as the kind which may have adversely damaged my entire career if I hadn’t come across your blog post. Your good talents and kindness in handling almost everything was vital. I don’t know what I would have done if I hadn’t come upon such a thing like this. I can now look forward to my future. Thanks for your time very much for this professional and amazing guide. I won’t be reluctant to endorse your web site to anybody who desires recommendations about this matter.

  2. I would like to show some appreciation to the writer for rescuing me from such a situation. Because of browsing through the online world and getting techniques which are not pleasant, I assumed my entire life was over. Living without the solutions to the difficulties you’ve fixed all through your entire post is a crucial case, as well as ones which may have adversely damaged my entire career if I hadn’t come across your web site. Your actual expertise and kindness in touching all the details was tremendous. I’m not sure what I would have done if I hadn’t discovered such a step like this. I am able to at this moment look forward to my future. Thank you very much for the reliable and effective help. I won’t hesitate to endorse your web blog to any person who ought to have guidance on this matter.

  3. I needed to compose you that very small observation to finally say thanks over again regarding the amazing information you have documented above. It has been extremely open-handed of you to provide openly precisely what most people could have made available as an e-book to make some profit for themselves, chiefly given that you could have tried it if you ever considered necessary. Those smart ideas additionally acted as the good way to be aware that some people have similar dreams like my own to find out a whole lot more on the subject of this issue. I am certain there are a lot more enjoyable instances in the future for people who view your website.

  4. I needed to post you a little remark so as to say thanks a lot as before for these superb methods you’ve shown on this page. This has been quite seriously generous with you to provide freely exactly what a number of us could have offered for sale as an electronic book to make some bucks for their own end, primarily given that you could possibly have tried it if you ever considered necessary. The thoughts additionally acted to become good way to recognize that most people have the identical interest just like my personal own to find out good deal more regarding this condition. Certainly there are several more enjoyable instances up front for people who see your blog post.

  5. My husband and i were absolutely joyful Albert managed to do his web research by way of the ideas he discovered in your web pages. It is now and again perplexing to just choose to be freely giving facts which many others have been trying to sell. And now we realize we have got the website owner to be grateful to for this. Most of the illustrations you have made, the straightforward web site navigation, the friendships you can help to promote – it’s got many unbelievable, and it’s leading our son in addition to the family believe that that content is cool, which is really essential. Thanks for the whole thing!

  6. I am commenting to make you know what a helpful discovery our child gained studying yuor web blog. She noticed some pieces, not to mention what it is like to possess a marvelous coaching heart to make others very easily grasp chosen problematic things. You really exceeded our desires. I appreciate you for providing these great, trustworthy, edifying not to mention unique tips on this topic to Kate.

  7. I just wanted to type a note in order to express gratitude to you for all of the precious guidelines you are writing on this site. My particularly long internet investigation has finally been rewarded with sensible content to write about with my co-workers. I would admit that most of us website visitors are unequivocally blessed to live in a fabulous community with many special professionals with interesting ideas. I feel very much happy to have come across the web site and look forward to tons of more amazing moments reading here. Thank you again for everything.

  8. I definitely wanted to compose a quick remark to say thanks to you for those stunning advice you are giving at this site. My rather long internet investigation has finally been honored with really good concept to exchange with my companions. I ‘d say that most of us website visitors actually are definitely fortunate to live in a great network with so many lovely people with great guidelines. I feel rather fortunate to have seen your site and look forward to really more enjoyable moments reading here. Thank you once again for everything.

  9. I simply needed to say thanks all over again. I do not know the things I would’ve worked on in the absence of these techniques contributed by you over that field. It had become a very difficult problem in my view, nevertheless discovering a new expert fashion you handled that took me to weep with gladness. I am just grateful for the advice and even expect you are aware of an amazing job you happen to be carrying out instructing the others thru your web page. I am certain you haven’t got to know any of us.

  10. I wish to express my appreciation to you for bailing me out of this instance. After surfing throughout the the web and finding concepts which were not pleasant, I believed my life was well over. Existing devoid of the strategies to the issues you’ve fixed all through your main write-up is a serious case, and ones that could have in a wrong way damaged my career if I had not discovered your site. Your personal knowledge and kindness in handling every part was priceless. I am not sure what I would have done if I hadn’t encountered such a solution like this. I can at this time look forward to my future. Thanks a lot so much for this high quality and result oriented help. I won’t think twice to propose your web page to anybody who ought to have assistance on this area.

  11. Thank you a lot for providing individuals with a very nice possiblity to read critical reviews from this web site. It is always so nice plus stuffed with a great time for me personally and my office mates to visit the blog more than 3 times per week to study the latest guidance you will have. Of course, I’m so at all times motivated with all the sensational suggestions you serve. Some 2 facts in this posting are honestly the most beneficial I have ever had.

  12. Revolutional update of SEO/SMM package “XRumer 16.0 + XEvil 3.0”:
    captcha recognition of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another categories of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other programms.

    Interested? There are a lot of impessive videos about XEvil in YouTube.
    Good luck!

    XRumer201707

  13. Thanks a lot for giving everyone a very wonderful chance to check tips from here. It can be so good and packed with amusement for me and my office mates to search your site on the least three times a week to study the latest secrets you have. And indeed, I am at all times astounded with all the mind-boggling hints you give. Selected 3 tips in this article are unquestionably the most efficient we have had.

  14. You made some first rate points there. I looked on the internet for the issue and found most people will go together with along with your website.

  15. It as not that I want to replicate your web page, but I really like the pattern. Could you tell me which style are you using? Or was it especially designed?

  16. you are really a good webmaster. The site loading speed is amazing. It seems that you are doing any unique trick. Also, The contents are masterwork. you have done a excellent job on this topic!

  17. I would like to show thanks to you for rescuing me from this type of matter. Because of looking throughout the world-wide-web and obtaining solutions which are not powerful, I was thinking my entire life was done. Being alive minus the solutions to the issues you’ve sorted out by way of your main guide is a critical case, and those which could have in a negative way damaged my career if I had not come across your website. That talents and kindness in touching all areas was very helpful. I am not sure what I would’ve done if I had not come across such a solution like this. I can now relish my future. Thank you so much for this professional and sensible guide. I won’t hesitate to endorse the sites to any person who requires recommendations about this topic.

  18. Absolutely NEW update of SEO/SMM package “XRumer 16.0 + XEvil 3.0”:
    captcha breaking of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another size-types of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other programms.

    Interested? You can find a lot of demo videos about XEvil in YouTube.
    See you later 😉

    XRumer201707

  19. Your style is very unique compared to other folks I have read stuff from. Many thanks for posting when you ave got the opportunity, Guess I will just book mark this web site.

  20. Revolutional update of SEO/SMM software “XRumer 16.0 + XEvil”:
    captcha recognition of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another types of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other software.

    Interested? You can find a lot of demo videos about XEvil in YouTube.
    See you later!

    XRumer20170717

  21. So happy to get located this submit.. Liking the post.. thanks alot So happy to possess identified this post.. So pleased to get found this submit..

  22. Absolutely NEW update of SEO/SMM package “XRumer 16.0 + XEvil”:
    captcha solution of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another categories of captcha,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other programms.

    Interested? There are a lot of impessive videos about XEvil in YouTube.
    See you later 😉

    XRumer20170718

  23. Wow, superb blog structure! How lengthy have you been blogging for? you make running a blog glance easy. The full glance of your web site is great, let alone the content!

  24. Absolutely NEW update of SEO/SMM software “XRumer 16.0 + XEvil 3.0”:
    captcha solving of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another subtypes of captcha,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other software.

    Interested? There are a lot of impessive videos about XEvil in YouTube.
    See you later 😉

    XRumer20170718

  25. This very blog is definitely entertaining additionally factual. I have picked many interesting advices out of this blog. I ad love to go back over and over again. Thanks a bunch!

  26. Very nice post. I just stumbled upon your weblog and wished to say that I have really enjoyed browsing your blog posts. In any case I will be subscribing to your feed and I hope you write again soon!

  27. My brother suggested I might like this website. He was entirely right. This post truly made my day. You cann at imagine just how much time I had spent for this information! Thanks!

  28. I simply desired to thank you very much once more. I do not know what I could possibly have sorted out in the absence of the type of pointers shared by you regarding that subject. It truly was a very troublesome condition in my circumstances, however , coming across a specialized manner you handled it forced me to cry over gladness. Now i’m happy for the service and even pray you recognize what a great job your are undertaking educating many people through your blog. Most likely you have never come across all of us.

  29. I happen to be writing to make you be aware of what a excellent discovery our daughter encountered checking your blog. She realized many pieces, including how it is like to have an awesome coaching spirit to make most people clearly fully understand specific complex subject matter. You undoubtedly did more than our own expectations. Thank you for showing such precious, trustworthy, edifying and even easy thoughts on your topic to Janet.

  30. Revolutional update of SEO/SMM software “XRumer 16.0 + XEvil 3.0”:
    captchas recognition of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another size-types of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other programms.

    Interested? You can find a lot of demo videos about XEvil in YouTube.
    See you later 😉

    XRumer20170721

  31. magnificent issues altogether, you just gained a emblem new reader. What may you recommend about your publish that you made some days ago? Any certain?

  32. Revolutional update of SEO/SMM package “XRumer 16.0 + XEvil”:
    captchas solution of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another size-types of captcha,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM software: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other programms.

    Interested? You can find a lot of demo videos about XEvil in YouTube.
    See you later!

    XRumer20170721

  33. Absolutely NEW update of SEO/SMM software “XRumer 16.0 + XEvil 3.0”:
    captchas solving of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another types of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other software.

    Interested? There are a lot of demo videos about XEvil in YouTube.
    See you later!

    XRumer20170721

  34. Revolutional update of SEO/SMM package “XRumer 16.0 + XEvil 3.0”:
    captcha breaking of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another categories of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other software.

    Interested? You can find a lot of demo videos about XEvil in YouTube.
    Good luck 😉

    XRumer20170721

  35. I wanted to post a brief remark to thank you for those stunning solutions you are sharing on this site. My time-consuming internet lookup has at the end of the day been recognized with wonderful facts and strategies to share with my family members. I would say that many of us website visitors actually are really lucky to dwell in a really good network with many awesome people with beneficial things. I feel quite lucky to have discovered your weblog and look forward to some more cool times reading here. Thanks a lot again for all the details.

Deixe uma resposta

O seu endereço de e-mail não será publicado.