在PySpark中使用Oracle查询中的参数,可以通过使用PySpark的JDBC连接器和Oracle数据库驱动程序来实现。下面是一个完整且全面的答案:
PySpark是Apache Spark的Python API,用于在大规模数据处理和分析中进行快速、分布式计算。Oracle是一种关系型数据库管理系统,被广泛应用于企业级数据存储和管理。
在PySpark中使用Oracle查询中的参数,可以按照以下步骤进行操作:
pyspark
和cx_Oracle
库。from pyspark.sql import SparkSession
spark = SparkSession.builder \
.appName("Oracle Parameterized Query") \
.config("spark.jars.packages", "oracle.jdbc.driver:ojdbc7:12.1.0.2") \
.getOrCreate()
请注意,上述代码中的spark.jars.packages
属性指定了Oracle数据库驱动程序的Maven坐标,以便在Spark中使用该驱动程序。
oracle_url = "jdbc:oracle:thin:@//hostname:port/service_name"
oracle_properties = {
"user": "your_username",
"password": "your_password",
"driver": "oracle.jdbc.driver.OracleDriver"
}
请替换hostname
、port
、service_name
、your_username
和your_password
为实际的Oracle数据库连接信息。
query = "SELECT * FROM your_table WHERE column = ?"
parameter = "parameter_value"
df = spark.read \
.format("jdbc") \
.option("url", oracle_url) \
.option("dbtable", query) \
.option("user", oracle_properties["user"]) \
.option("password", oracle_properties["password"]) \
.option("driver", oracle_properties["driver"]) \
.option("oracle.jdbc.BindByName", "true") \
.option("oracle.jdbc.J2EE13Compliant", "true") \
.option("oracle.jdbc.mapDateToTimestamp", "false") \
.option("oracle.jdbc.useFetchSizeWithLongColumn", "true") \
.option("oracle.jdbc.fanEnabled", "false") \
.option("oracle.net.CONNECT_TIMEOUT", "5000") \
.option("oracle.net.READ_TIMEOUT", "5000") \
.option("oracle.net.ssl_server_dn_match", "true") \
.option("oracle.net.authentication_services", "(TCPS,TCPS1)") \
.option("oracle.net.ssl_version", "1.2") \
.option("oracle.net.trace_level", "OFF") \
.option("oracle.net.encryption_client", "REQUIRED") \
.option("oracle.net.crypto_checksum_client", "REQUIRED") \
.option("oracle.jdbc.trace", "false") \
.option("oracle.jdbc.useNio", "true") \
.option("oracle.jdbc.TcpNoDelay", "false") \
.option("oracle.jdbc.maxRows", "0") \
.option("oracle.jdbc.defaultRowPrefetch", "10") \
.option("oracle.jdbc.v$session.program", "your_program_name") \
.option("oracle.jdbc.v$session.osuser", "your_os_username") \
.option("oracle.jdbc.v$session.machine", "your_machine_name") \
.option("oracle.jdbc.v$session.process", "your_process_name") \
.option("oracle.jdbc.v$session.module", "your_module_name") \
.option("oracle.jdbc.v$session.client_info", "your_client_info") \
.option("oracle.jdbc.remarksReporting", "false") \
.option("oracle.jdbc.defaultLobPrefetchSize", "4000") \
.option("oracle.jdbc.tns_admin", "/path/to/tnsnames.ora") \
.option("oracle.jdbc.xa.accounting", "false") \
.option("oracle.jdbc.notificationPort", "0") \
.option("oracle.jdbc.fetchSize", "100") \
.option("oracle.jdbc.useThreadLocalBufferCache", "true") \
.option("oracle.jdbc.useOCIDefaultDefines", "false") \
.option("oracle.jdbc.flushRowPrefetch", "false") \
.option("oracle.jdbc.LobStreamPosStandardCompliant", "true") \
.option("oracle.jdbc.TcpAbortTimeout", "0") \
.option("oracle.jdbc.jmxEnabled", "true") \
.option("oracle.jdbc.mapDateTimestampToTimestamp", "true") \
.option("oracle.jdbc.driver.excludedevicelinks", "") \
.option("oracle.jdbc.maxStatementsPerConnection", "-1") \
.option("oracle.jdbc.implicitStatementCacheSize", "-1") \
.option("oracle.jdbc.enableClearTextPasswords", "false") \
.option("oracle.jdbc.debugShardingKey", "") \
.option("oracle.jdbc.enableFanout", "false") \
.option("oracle.jdbc.mapDateToTimestamp", "true") \
.option("oracle.net.authentication_services", "(NTS)") \
.option("oracle.net.ssl_cipher_suites", "") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.jdbc.fanEnabledCacheServerList", "") \
.option("oracle.jdbc.mapDoubleToFloat", "false") \
.option("oracle.jdbc.tns_admin", "/path/to/tnsnames.ora") \
.option("oracle.jdbc.dynamicRefCursorSizes", "") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.jdbc.suspendThreshold", "-1") \
.option("oracle.jdbc.useFetchSizeWithLongColumn", "true") \
.option("oracle.jdbc.maxCachedBufferSize", "-1") \
.option("oracle.jdbc.maxCachedBuffersize", "-1") \
.option("oracle.jdbc.autoCommitSpecCompliant", "false") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.jdbc.nonTxnBinding", "true") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.jdbc.defaultExecuteBatch", "1") \
.option("oracle.jdbc.mapDateToTimestamp", "true") \
.option("oracle.net.encryption_types_client", "RC4_128,RC4_56,RC4_40,RC4_256,RC2_40,RC2_128,DES40CBC,DES56C BC,DES192EDE3CBC,DES40C BC,DES64C BC,DES192CBC,DES56CBC,DES168CBC,3DES168EDE3CBC,3DES112,3DES168,3DES24,3DES112EDE3CBC, 3DES24EDE3CBC") \
.option("oracle.net.crypto_checksum_types_client", "MD5,SHA-1") \
.option("oracle.net.jndiexceptionlistener", "") \
.option("oracle.jdbc.events", "") \
.option("oracle.net.authentication_krb5_realm", "your_krb5_realm") \
.option("oracle.jdbc.LobStreamPosStandardCompliant", "true") \
.option("oracle.jdbc.largePulleyChassis", "") \
.option("oracle.jdbc.sqlTranslationQueryTimeout", "0") \
.option("oracle.jdbc.implicitStatementCacheSize", "-1") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.jdbc.largePulleyPullTimeout", "-1") \
.option("oracle.net.ssl_keymanagerfactory_algorithm", "your_ssl_keymanagerfactory_algorithm") \
.option("oracle.net.encryption_client", "REQUIRED") \
.option("oracle.jdbc.allowUCS2InBufferCache", "false") \
.option("oracle.jdbc.mapNCharToNVarchar", "true") \
.option("oracle.net.ssl_cipher_suites", "") \
.option("oracle.net.authentication_krb5_realm", "your_krb5_realm") \
.option("oracle.net.encryption_types_client", "RC4_128,RC4_56,RC4_40,RC4_256,RC2_40,RC2_128,DES40CBC,DES56C BC,DES192EDE3CBC,DES40C BC,DES64C BC,DES192CBC,DES56CBC,DES168CBC,3DES168EDE3CBC,3DES112,3DES168,3DES24,3DES112EDE3CBC, 3DES24EDE3CBC") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.ssl_server_dn_match", "true") \
.option("oracle.net.encryption_client", "REQUIRED") \
.option("oracle.jdbc.disableOob", "false") \
.option("oracle.net.ssl_version", "1.2") \
.option("oracle.net.authentication_services", "(TCPS,TCPS1)") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.jdbc.mapDateToTimestamp", "true") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_services", "(NTS)") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.disableOob", "false") \
.option("oracle.net.ssl_cipher_suites", "") \
.option("oracle.net.ssl_server_dn_match", "true") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_realm", "your_krb5_realm") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.option("oracle.net.authentication_krb5_conf", "/path/to/krb5.conf") \
.option("oracle.net.authentication_krb5_kinit_command", "/path/to/kinit") \
.option("oracle.net.authentication_krb5_service", "your_krb5_service_name") \
.option("oracle.net.authentication_krb5_keytab", "/path/to/krb5_keytab") \
.option("oracle.net.authentication_krb5_mutual", "false") \
.option("oracle.net.authentication_krb5_debug", "false") \
.load()
请替换your_table
为实际的表名,column
为实际的列名,parameter_value
为实际的参数值。
这些选项中包含了许多Oracle数据库连接属性,可以根据实际情况进行调整和配置。请注意,这些选项仅供参考,并非全部都是必需的。
df.show()
以上代码将打印查询结果。
这就是在PySpark中使用Oracle查询中的参数的完善且全面的答案。
腾讯云提供了与云计算相关的多个产品和服务,适用于不同的应用场景。以下是一些推荐的腾讯云产品和产品介绍链接地址,可以根据需要选择合适的产品:
请注意,以上链接仅供参考,并非全部都是与PySpark和Oracle查询相关的产品。可以根据实际需求和具体场景进行选择和使用。
领取专属 10元无门槛券
手把手带您无忧上云