~hrbrmstr/sergeant

ref: facda3264662b7dc8bb423fbaadb6fd2fa7635fe sergeant/man/drill_custom_functions.Rd -rw-r--r-- 3.9 KiB
facda326hrbrmstr NEWS 9 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 \code{dplyr} API: 
\code{\link{src_drill}()},
\code{\link{src_tbls.src_drill}()}
}
\concept{Drill REST \code{dplyr} API}