SQL connection setup on Linux Server for R

SQL connection setup on Linux Server for R

# Shows setup for both SQL Server and MySQL

## SQL Server

Connecting to SQL server from linux (Useful answer: https://stackoverflow.com/a/51266453/4198868):

First, get Server's IP. In linux / Terminal in Rstudio use

        ping PSC-CPT-SQL2

            * Note IP in brackets.

Install SQL Server (see here: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15)

    sudo su curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
            * My version now: Ubuntu 18.04
    curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
    sudo apt-get update
    sudo ACCEPT_EULA=Y apt-get install msodbcsql17
    
In R now:

library(pacman)
pacman::p_load(tidyverse)
pacman::p_load(DBI)
con <- dbConnect(odbc::odbc(), 
                 .connection_string = "Driver={ODBC Driver 17 for SQL Server};Server=192.168.4.203; Database=Psec_Equities;Uid=UID; Pwd=PW", 
                 timeout = 10)

tbl(con, "IndexCloses") %>% tbl_df()


## MySQL:
   
   From here: https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/
   
   sudo curl -sLO https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb && sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb
   
        * After prompt, go OK and OK
   
   sudo apt-get update
   apt install mysql-client-core-5.7
   
* Back in R:
    devtools::install_github("r-dbi/RMySQL")   
    mydb <-  dbConnect(RMySQL::MySQL(), user = USER, password = PW,
                   dbname = DBName, host = IP_from_ping)
   

Also - ensure dttm of date column:

df %>%
mutate(effectivedatetime = gsub(":", "-", effectivedatetime)) %>%
mutate(effectivedatetime = lubridate::ymd_hms(effectivedatetime) ) %>%