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 ?

asked 2 years ago423 views
2 Answers
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 ;)

answered a year ago
-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.

answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions