~hrbrmstr/sergeant

ref: 09a853d2ab368e812d96a81c418129840feeda9c sergeant/man/drill_custom_functions.Rd -rw-r--r-- 3.9 KiB
09a853d2hrbrmstr v0.9.0 8 months ago
                                                                                
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
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/custom.r
\name{drill_custom_functions}
\alias{drill_custom_functions}
\title{Drill expressions / custom functions \code{dplyr} translations}
\description{
One benefit of \code{dplyr} is that it provide a nice DSL over datasbase ops but that
means there needs to be knowlege of functions supported by the host database and
then a translation layer so they can be used in R.
}
\details{
Similarly, there are functions like \code{grepl()} in R that don't directly exist in
databases. Yet, one can create a translation for \code{grepl()} that maps to a
\href{https://github.com/parisni/drill-simple-contains}{Drill custom function} so you
don't have to think differently or rewrite your pipes when switching from core
tidyverse ops and database ops.

Many functions translate on their own, but it's handy to provide explicit ones,
especially when you want to use parameters in a different order.

If you want a particular custom function mapped, file a PR or issue request in
the link found in the \code{DESCRIPTION} file.
\itemize{
\item \code{as.character(x)} : \verb{CAST( x AS CHARACTER )}
\item \code{as.integer64(x)} : \verb{CAST( x AS BIGINT )}
\item \code{as.date(x)} : \verb{CAST( x AS DATE )}
\item \code{as.logical(x)} : \verb{CAST( x AS BOOLEAN) }
\item \code{as.numeric(x)} : \verb{CAST( x AS DOUBLE )}
\item \code{as.posixct(x)} : \verb{CAST( x AS TIMESTAMP )}
\item \code{binary_string(x)} : \code{BINARY_STRING( x )}
\item \code{cbrt(x)} : \code{CBRT( x )}
\item \code{char_to_timestamp(x, y)} : \code{TO_TIMESTAMP( x, y )}
\item \code{grepl(y, x)} : \code{CONTAINS( x, y )}
\item \code{contains(x, y)} : \code{CONTAINS( x, y )}
\item \code{convert_to(x, y)} : \code{CONVERT_TO( x, y )}
\item \code{convert_from(x, y)} : \code{CONVERT_FROM( x, y )}
\item \code{degrees(x)} : \code{DEGREES( x )}
\item \code{lshift(x, y)} : \code{DEGREES( x, y )}
\item \code{negative(x)} : \code{NEGATIVE( x )}
\item \code{pow(x, y)} : \code{MOD( x, y )}
\item \code{sql_prefix(x, y)} : \code{POW( x, y )}
\item \code{string_binary(x)} : \code{STRING_BINARY( x )}
\item \code{radians(x)} : \code{RADIANS( x )}
\item \code{rshift(x)} : \code{RSHIFT( x )}
\item \code{to_char(x, y)} : \verb{TO_CHAR  x, y )}
\item \code{to_date(x, y)} : \code{TO_DATE( x, y )}
\item \code{to_number(x, y)} : \code{TO_NUMBER( x, y )}
\item \code{trunc(x)} : \code{TRUNC( x )}
\item \code{double_to_timestamp(x)} = \code{TO_TIMESTAMP( x )}
\item \code{char_length(x)} = \code{CHAR_LENGTH( x )}
\item \code{flatten(x)} = \code{FLATTEN( x )}
\item \code{kvgen(x)} = \code{KVGEN( x )}
\item \code{repeated_count(x)} = \code{REPEATED_COUNT( x )}
\item \code{repeated_contains(x)} = \code{REPEATED_CONTAINS( x )}
\item \code{ilike(x, y)} = \code{ILIKE( x, y )}
\item \code{init_cap(x)} = \code{INIT_CAP( x )}
\item \code{length(x)} = \code{LENGTH( x )}
\item \code{lower(x)} = \code{LOWER( x )}
\item \code{tolower(x)} = \code{LOWER( x )}
\item \code{ltrim(x, y)} = \code{LTRIM( x, y )}
\item \verb{nullif(x, y} = \code{NULLIF( x, y )}
\item \code{position(x, y)} = \verb{POSITION( x IN  y )}
\item \code{gsub(x, y, z)} = \code{REGEXP_REPLACE( z, x, y )}
\item \code{regexp_replace(x, y, z)} = \code{REGEXP_REPLACE( x, y, z )}
\item \code{rtrim(x, y)} = \code{RTRIM( x, y )}
\item \code{rpad(x, y)} = \code{RPAD( x, y )}
\item \code{rpad_with(x, y, z)} = \code{RPAD( x, y, z )}
\item \code{lpad(x, y)} = \code{LPAD( x, y )}
\item \code{lpad_with(x, y, z)} = \code{LPAD( x, y, z )}
\item \code{strpos(x, y)} = \code{STRPOS( x, y )}
\item \code{substr(x, y, z)} = \code{SUBSTR( x, y, z )}
\item \code{upper(x)} = \code{UPPER(1)}
\item \code{toupper(x)} = \code{UPPER(1)}
}

You can get a compact list of these with:

\code{sql_translate_env(src_drill()$con)}

as well.
}
\seealso{
Other Drill REST API (dplyr): 
\code{\link{src_drill}()},
\code{\link{src_tbls.src_drill}()}
}
\concept{Drill REST API (dplyr)}