---
title: "多语言混合使用分析报告示例"
author: "Jean"
date: "`r Sys.Date()`"
output: html_document
runtime: shiny
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

### 在R markdown中混合使用R、Python、SQL、JavaScript、HTML、CSS等6种语言

# 加载用到的包
加载reticulate包以连通R与Python会话，R markdown中只有R、Python、Julia三种语言的会话是连续的，即前面代码块中定义的变量，后面的代码块中可以引用。reticulate包让R与Python包中的变量可以互相引用。R markdown中的SQL代码块依赖于DBI包，支持DBI API的数据库，如Oracle，SQLite可以在SQL代码块中直接执行SQL语句，并且执行结果可以返回成R data frame，相当于R、Python、SQL在R markdown是连通的。
其它的语言，可以在R、或Python中分别调用，它们的会话是独立的，即前面代码块中定义的变量，后面的代码块中不能引用。但可以通过把结果赋值给R或Python变量，通过它们把各种语言不同的代码块连接起来，从而达到看起来像是在同一个会话中的效果。
```{r,  message=FALSE}
library(shiny)
library(reticulate)
library(DBI)
library(RODPS)
library(ggplot2)
library(plotly)
library(showtext)
# 支持ggplot2绘图中文标注
showtext_auto()
t1<- proc.time()
```


# 先从各个数据源加载数据
以航空乘客数据airpassengers为例,从各个数据源加载数据，然后插入SQLite内存数据库中，再执行SQL语句分析。
实际操作中连接数据源后，可以直接执行SQL语句操作数据。因为有多个数据源，这里用SQLite做演示。

大数据运算在云端等强算力环境上完成，Rstudio  Server、Shiny等适合处理预处理好的中间结果小数据集。


## 从Oracle加载数据
```{r, eval=FALSE}
# 如果连接Oracle
library(ROracle)
## create a Oracle instance and create one connection.
ora <- Oracle()         ## or dbDriver("Oracle")
con <- dbConnect(ora, username = "user", password = "password", dbname = "SID")

rs   <- dbSendQuery(con, "select * from airpassengers ")
passengers <- fetch(rs, n = -1)
dbClearResult(rs)       ## done with this query

```


## 从阿里云载入数据
```{r}
passengers<-rodps.sql("select * from airpassengers") 
names(passengers)<-c("month","passengers")
knitr::kable(head(passengers,5))
```


## 从华为云载入数据

### 设置local
```{python}
from dli.dli_client import DliClient
from obs import *
#这是自己添加的自定义python模块，存放访问DLI的access key与secret key，因为源码是网上可见的。
from dli import dlikeys
from obs import obskeys
from datetime import datetime
import locale
import time
import os
import sys
import pandas as pd
from io import BytesIO
import numpy as np

locale.setlocale(locale.LC_ALL, 'en_US.UTF-8')
print(locale.getlocale())


GMT_DATE_FORMAT = '%a, %d %b %Y %H:%M:%S GMT'
LONG_DATE_FORMAT = '%Y%m%dT%H%M%SZ'
SHORT_DATE_FORMAT = '%Y%m%d'
EXPIRATION_DATE_FORMAT = '%Y-%m-%dT%H:%M:%SZ'

now_date = datetime.utcnow()
str_date = now_date.strftime(GMT_DATE_FORMAT)
print(str_date)
```

### 初始化OBS client
```{python}
def init_obs_client():
    AK = obskeys.obsAK
    SK = obskeys.obsSK
    server = 'https://obs.cn-south-1.myhuaweicloud.com'
    # Constructs a obs client instance with your account for accessing OBS
    obsClient = ObsClient(access_key_id=AK, secret_access_key=SK, server=server)
    return obsClient

db_name = 'geshui'
queue_name = 'default'
bucketName = 'gsmx'

obsClient = init_obs_client()

```

### 从OBS加载数据
```{python}
key = 'airpassengers.csv'
cols = ["month","passengers"]; types = {"month":str,"passengers":int}
resp = obsClient.getObject(bucketName, key, loadStreamInMemory=True)
fileBytes = BytesIO( resp.body.buffer )
passengers = pd.read_csv(fileBytes,header=1,names=cols,dtype=types,encoding='utf-8')
# print(passengers)
```

### 在R脚本中引用Python变量
通过加'py$'前缀引用。
```{r}
knitr::kable(head(py$passengers,5))
```

### 在Python中引用R变量
通过加'r.'前缀引用。
```{python}
print(r.passengers[0:5])
```


### 设置数据湖客户端
```{python}
def init_aksk_dli_client():
    auth_mode = 'aksk'
    region = 'cn-south-1'
    project_id = dlikeys.dli_project_id
    ak = dlikeys.dliAK
    sk = dlikeys.dliSK
    dli_client = DliClient(auth_mode=auth_mode, region=region, project_id=project_id,ak=ak, sk=sk)
    return dli_client

def run_sql(dli_client, db_name, queue_name, sql):
    # execute SQL
    try:
        sql_job = dli_client.execute_sql(sql, db_name)
        result_set = sql_job.get_result()
    except DliException as e:
        print(e)
        return

    if result_set.row_count == 0:
        return None

    return result_set

dli_client = init_aksk_dli_client()

```


### 从数据湖DLI加载数据
```{python}
db_name = 'geshui'
queue_name = 'default'
# OBS bucket name
bucketName = 'gsmx'

t1 = time.time()
month = [] ; passengers=[]
sql = "select month,passengers from airpassengers;"
result_set = run_sql(dli_client, db_name, queue_name, sql)
print("loading data......")
for row in result_set:
    cols = row.columns
    month.append(cols[0])
    passengers.append(cols[1])
t2 = time.time()
print("time: ",t2-t1)

# 从 list建立 pandas data frame
passengers = pd.DataFrame({"month":month,"passengers":passengers})
print(passengers[0:5])
```


## 数据注入SQLite

```{r message=FALSE}

db <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
```

### 
如果SQL代码块上的运行按钮不见了，或者运行SQL代码块时出现错误"Error: unexpected symbol"
按该帖子panporter描述的办法解决。

https://github.com/rstudio/rstudio/issues/6220

Steps to reproduce:

    1.Create a simple RMD file with one SQL chunk.
    
    2.Switch "Global Options - R Markdown - Show output inline for all R Markdown documents" OFF.
    
    3.Run SQL chunk.
    
      Results in error in console:
      
          Error: unexpected string constant in "SELECT 1"
          
    4.Reload file: The play button disappears.
    
    5.Switch "Global Options - R Markdown - Show output inline for all R Markdown documents" back ON.
    
      And reload file.
      
      Play button reappears. SQL chunk runs as expected.
      
This seems to be origin of problem #8786 as well.

My setting:
RStudio
Version 1.4.1717
Linux and Windows

```{sql, connection=db}
DROP TABLE IF EXISTS airpassengers
```

### 引用Python返回的数据
```{r}
dbWriteTable(db,"airpassengers", py$passengers)
```

### 执行SQL语句

如果没有指定output.var选项，SQL语句执行结果显示在文档输出中。
```{sql connection=db}
SELECT * FROM airpassengers;
```

如果指定了output.var选项，SQL语句执行结果不会显示在文档输出中，而是保存到指定的R变量中。
下面按年统计各年乘客的累计数量，存入y_sum data frame中。
```{sql connection=db, output.var="y_sum"}
SELECT substring(month,1,4) as year, sum(passengers) as passengers
  from airpassengers
  group by substring(month,1,4)
  order by substring(month,1,4)
```

## 在Python中绘图

```{python}
import matplotlib.pyplot as plt
# fig = plt.figure(figsize = (3, 2))  # 没有这一行，Rstudio中会显示在Plots窗口
plt.bar(r.y_sum["year"],r.y_sum["passengers"], color="orange")   # 通过"r."前缀引用R中的变量
plt.xlabel("年度")
plt.ylabel("累计乘客")
plt.title("航空乘客统计")
plt.xticks(rotation=30)
plt.show()
```

## HTML widgets

HTML widgets框架由R 软件包htmlwidgets实现，提供了browser端交互式的控件，它与Shiny反应式控件的区别是，这些widget是纯JavaScript实现，它的反应只在浏览器端，不涉及服务器端。而Shiny的反应式UI组件在浏览器端依赖于htmlwidgets包，在服务器端实现反应机制。

此处用ggplotly画一个交互式的条形图，由JavaScript库ploty支持，在条形图上移动鼠标会显示相应的数据。
```{r}
# https://stackoverflow.com/questions/13067751/how-to-run-r-scripts-on-servers-without-x11
options(bitmapType='cairo')  # 服务器端没有X11支持，要打开这个选项。
p <- ggplot(data = y_sum, aes(x = year, y = passengers)) +
          geom_bar(stat="identity", color="orange", fill="orange")+
          ggtitle("航空乘客统计图")+
          xlab("年度")+
          ylab("乘客数量")

ggplotly(p)
```


## Shiny 文档

交互式文档，画选定年份的航空乘客数量条形图。


### 选择年度
```{r}
selectInput(
  'year', label = '年度',
  choices = y_sum["year"], selected = "1950"
)
```

### 提取年度数据
```{r}
month_passengers<-reactive({py$passengers[which(substr(py$passengers$month,1,4)==input$year),]})
```


### 画图
```{r}
renderPlot({
   ggplot(data = month_passengers(), aes(x = substr(month,6,7), y = passengers)) +
          geom_bar(stat="identity", color="orange", fill="orange")+
          ggtitle("航空乘客统计图")+
          xlab("年度")+
          ylab("乘客数量")
})
```


### 画表
```{r}
renderTable(month_passengers())
```


## JavaScript & CSS

### Javascript把报告标题的颜色设置为红色
```{js}
$('.title').css('color', 'red')
```

### CSS
把各级标题的颜色设置为红色，段落字体大小设为16点阵。
```{css, echo=TRUE}
h1, h2, h3, h4 { 
  color: red;
}

p {
   font-size: 16px;
}
```

### HTML
在页面中直接插入HTML代码。
```{r}
HTML("<div style='color: red; font-size: 20px; text-align: center;'>")
```
总耗时：
```{r}
t2<-proc.time()
cat(round(t2-t1,1))
HTML("<img src='wukong.webp', height=300>")
HTML("</div>")
```


