-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathindex.Rmd
148 lines (111 loc) · 4.58 KB
/
index.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
---
title: "How to use the URL data source"
---
# Determine the URL of your JSON data
For this example, we're using the [NICVA Marine Litter Survey](https://dbhub.io/nicva/Marine%20Litter%20Survey%20(Keep%20Northern%20Ireland%20Beautiful).sqlite) database.
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/000_dbhub_source_database1.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/010_dbhub_source_database2.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/020_dbhub_source_database3.png")
```
# In Redash, open Data Sources Settings
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/030_redash_welcome_page1.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/040_redash_welcome_page2.png")
```
# Create a new Data Source
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/050_redash_new_data_source0.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/070_redash_new_data_source2.png")
```
# Fill out the details for the data source
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/080_redash_new_data_source_url1.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/090_redash_new_data_source_url2.png")
```
# Create another new Data Source
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/100_redash_data_sources2.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/105_redash_new_data_source2.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/110_redash_new_data_source_query_mapper1.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/120_redash_new_data_source_query_mapper2.png")
```
# Create the Source query
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/125_redash_new_query_select_data_source0.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/130_redash_new_query_select_data_source1.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/140_redash_new_query_select_data_source2.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/145_redash_new_query_select_data_source2b.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/150_redash_new_query_select_data_source3.png")
```
# Create the visualisation query
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/160_redash_new_query_select_data_source4.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/170_redash_new_query2_select_data_source1.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/180_redash_new_query2_select_data_source2.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/190_redash_new_query2_select_data_source3.png")
```
The SQL in the above screenshot is:
```
SELECT
substr(Date, 7, 4) || '-' || substr(Date, 4, 2) || '-' || substr(Date, 1, 2) as Date,
Category,
sum(distinct Number) as SumVal
FROM query_1
GROUP BY 1, 2;
```
Take special care of the FROM clause: `FROM query_1`. The number `1` in `query_1`
is the query # you saved before.
So, if the number of the query you saved in the previous step was 234, your SQL
query will need a `FROM` clause of `FROM query_234`.
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/200_redash_new_query2_select_data_source4.png")
```
```{r, echo = FALSE, fig.align='center', out.width='90%'}
knitr::include_graphics("images/210_redash_new_query2_select_data_source5.png")
```
<!-- Fathom - simple website analytics - https://github.com/usefathom/fathom -->
<script>
(function(f, a, t, h, o, m){
a[h]=a[h]||function(){
(a[h].q=a[h].q||[]).push(arguments)
};
o=f.createElement('script'),
m=f.getElementsByTagName('script')[0];
o.async=1; o.src=t; o.id='fathom-script';
m.parentNode.insertBefore(o,m)
})(document, window, '//stats.sqlitebrowser.org/tracker.js', 'fathom');
fathom('set', 'siteId', 'MBTSF');
fathom('trackPageview');
</script>
<!-- / Fathom -->