AWS Glue Studio to Redshift

0

Hello everyone !

I am trying to import data from one RDS SQL Server to Redshift but I have a query with a recursive cte like that :

WITH RECURSIVE table(n) AS (
    select 1
  UNION ALL
    SELECT n+1 FROM table WHERE n < 100
)
SELECT n FROM table;

On Redshift there is no error but if I execute the query in an aws glue job (Transform - SQL Query) then i get this error :

ParseException: no viable alternative at input 'WITH RECURSIVE table

What am I missing ?

gefragt vor einem Jahr359 Aufrufe
2 Antworten
0

Hello,

Thanks for your answer ! You are right glue doesn't support recursive function, thus i created a view in SQL server. Then i imported this view as a table in glue and replaced te recusrive function in the script ;)

beantwortet vor einem Jahr
-1

Hello,

I would like to inform you that Glue/Spark doesn't support recursive query. But you can use spark dataframe with the wildcard to load data from the recursive way.

Let say you have data inside the following s3 path: s3://kg-testing/test2/2018/07/10/*.files.

Then you can use the following glue code to read it recursively using wildcard (*).

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

## @params: [JOB_NAME]
args = getResolvedOptions(sys.argv, ['JOB_NAME'])

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

print("**********DF printing*********")
spark.read .csv("s3://kg-testing/test2/*/*/*/*").createOrReplaceTempView("testsample")

sqlDF=spark.sql("select * from testsample")
print(sqlDF.show ())

In order for me to troubleshoot further, by taking a look at the logs in the backend, please feel free to open a support case with AWS using the following link with the sanitized script, the job run and we would be happy to help.

beantwortet vor einem Jahr

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen