数组索引以 0 ```
with t as (select 'Now.I.heard.you.know.that.secret.chord' as mycol)

select split(t.mycol,'\.') as arr
,split(t.mycol,'\.')[0] as arr_1st_element
,split(t.mycol,'\.')[1] as arr_2nd_element
,split(t.mycol,'\.')[2] as arr_3nd_element

from t
;

+----------------------------------------------------------+-----------------+-----------------+-----------------+
| arr | arr_1st_element | arr_2nd_element | arr_3nd_element |
+----------------------------------------------------------+-----------------+-----------------+-----------------+
| ["Now","I","heard","you","know","that","secret","chord"] | Now | I | heard |
+----------------------------------------------------------+-----------------+-----------------+-----------------+

至少目前不支持数组切片。如果你想切片，就在切片前做

with t as (select 'Now.I.heard.you.know.that.secret.chord' as mycol)

select split(substring_index(substring_index(t.mycol,'.',7),'.',-3),'\.') as slice_option_1
,split(regexp_extract(t.mycol,'(.?\.){4}((\.?[^.]){0,3})',2),'\.') as slice_option_2

from t
;

+--------------------------+--------------------------+
| slice_option_1 | slice_option_2 |
+--------------------------+--------------------------+
| ["know","that","secret"] | ["know","that","secret"] |
+--------------------------+--------------------------+