AWS Glueでサブクエリを使う



lowerBound、upperBoundだけではカラムや行の絞り込みができないので試したらできました。
DATABASE_USER = 'USER'
DATABASE_PASSWORD = 'PASSWORD'
# TABLE_NAME = 'MY_TABLE'
PARTITION_COLUMN = 'ID'

query = "(SELECT max({0}), min({0}) FROM {1}) sub".format(
    PARTITION_COLUMN, TABLE_NAME
)
properties = {
    "user": DATABASE_USER,
    "password": DATABASE_PASSWORD,
}
​(upper_bound, lower_bound) = (spark.read
    .jdbc(url=JDBC_URL, table=query, properties=properties)
    .first())

SUB = """
(
  SELECT
   id, 
   name
  FROM MY_TABLE
  WHERE
   id >= 1000
)
"""

df = spark \
    .read \
    .format("jdbc") \
    .option("url", JDBC_URL) \
    .option("user", DATABASE_USER) \
    .option("password", DATABASE_PASSWORD) \
    .option("dbtable", SUB) \
    .option("numPartitions", 100) \
    .option("partitionColumn", PARTITION_COLUMN ) \
    .option("lowerBound", lower_bound) \
    .option("upperBound", upper_bound) \
    .load()