Sparklyr / Hive: how to use regexp (regexp_replace) correctly?
Consider the following example
dataframe_test<- data_frame(mydate = c('2011-03-01T00:00:04.226Z', '2011-03-01T00:00:04.226Z'))
# A tibble: 2 x 1
mydate
<chr>
1 2011-03-01T00:00:04.226Z
2 2011-03-01T00:00:04.226Z
sdf <- copy_to(sc, dataframe_test, overwrite = TRUE)
> sdf
# Source: table<dataframe_test> [?? x 1]
# Database: spark_connection
mydate
<chr>
1 2011-03-01T00:00:04.226Z
2 2011-03-01T00:00:04.226Z
I would like to change the symbol timestamp
so that it has a more conventional format. I tried to do it with help regexp_replace
but it doesn't work.
> sdf <- sdf %>% mutate(regex = regexp_replace(mydate, '(\\d{4})-(\\d{2})-(\\d{2})T(\\d{2}):(\\d{2}):(\\d{2}).(\\d{3})Z', '$1-$2-$3 $4:$5:$6.$7'))
> sdf
# Source: lazy query [?? x 2]
# Database: spark_connection
mydate regex
<chr> <chr>
1 2011-03-01T00:00:04.226Z 2011-03-01T00:00:04.226Z
2 2011-03-01T00:00:04.226Z 2011-03-01T00:00:04.226Z
Any ideas? What is the correct syntax?
source to share
Spark SQL and Hive provide two different features:
-
regexp_extract
- which takes a string, pattern, and group index to retrieve. -
regexp_replace
- which takes a string, a pattern, and a replacement string.
The first can be used to highlight a separate group with the same index semantics as forjava.util.regex.Matcher
The regexp_replace
whole string must match for the pattern , and if there is no match, the input string is returned:
sdf %>% mutate(
regex = regexp_replace(mydate, '^([0-9]{4}).*', "$1"),
regexp_bad = regexp_replace(mydate, '([0-9]{4})', "$1"))
## Source: query [2 x 3]
## Database: spark connection master=local[8] app=sparklyr local=TRUE
##
## # A tibble: 2 x 3
## mydate regex regexp_bad
## <chr> <chr> <chr>
## 1 2011-03-01T00:00:04.226Z 2011 2011-03-01T00:00:04.226Z
## 2 2011-03-01T00:00:04.226Z 2011 2011-03-01T00:00:04.226Z
and when regexp_extract
not required:
sdf %>% mutate(regex = regexp_extract(mydate, '([0-9]{4})', 1))
## Source: query [2 x 2]
## Database: spark connection master=local[8] app=sparklyr local=TRUE
##
## # A tibble: 2 x 2
## mydate regex
## <chr> <chr>
## 1 2011-03-01T00:00:04.226Z 2011
## 2 2011-03-01T00:00:04.226Z 2011
Also, due to indirect execution (R -> Java), you will have to exit twice:
sdf %>% mutate(
regex = regexp_replace(
mydate,
'^(\\\\d{4})-(\\\\d{2})-(\\\\d{2})T(\\\\d{2}):(\\\\d{2}):(\\\\d{2}).(\\\\d{3})Z$',
'$1-$2-$3 $4:$5:$6.$7'))
Spark datetime functions are commonly used:
spark_session(sc) %>%
invoke("sql",
"SELECT *, DATE_FORMAT(CAST(mydate AS timestamp), 'yyyy-MM-dd HH:mm:ss.SSS') parsed from dataframe_test") %>%
sdf_register
## Source: query [2 x 2]
## Database: spark connection master=local[8] app=sparklyr local=TRUE
##
## # A tibble: 2 x 2
## mydate parsed
## <chr> <chr>
## 1 2011-03-01T00:00:04.226Z 2011-03-01 01:00:04.226
## 2 2011-03-01T00:00:04.226Z 2011-03-01 01:00:04.226
but unfortunately sparklyr
seems to be extremely limited in this area and treats timestamps as strings.
See also change line in DF with hive command and mutate with sparklyr .
source to share