如何从谷歌云函数连接到甲骨文数据库

Question

如何从Google Cloud Function(Python(连接到Oracle数据库？

我尝试使用jdebeapi，它给了我以下错误

File "/env/local/lib/python3.7/site-packages/jpype/_jvmfinder.py", line 97, in find_possible_homes
for childname in sorted(os.listdir(parent)):
FileNotFoundError: [Errno 2] No such file or directory: '/usr/lib/jvm'

Answer 1

是的，我试过了，我写了一篇关于这个的文章，我将在四月的谷歌下一个SF(会话srv 212(上讨论它。

TL;DR：这是不可能的，因为你必须使用即时客户端，你不能在功能上安装它。将 Cloud Run 或 Java 运行时用于 Cloud Functions(目前为 alpha 版(。

Answer 2

FileNotFoundError

FileNotFoundError: [Errno 2] No such file or directory: '/usr/lib/jvm'

此错误表明运行时环境没有 JaydebeApi 库连接到数据库引擎所需的 jvm 库。

因此，您可以手动放置此库，然后在建立连接之前将 ENV 变量设置为"JAVA_HOME"。

如何？

解决方法：由于 python 3.7 的运行时环境是Ubuntu 1804(不确定，您可以使用子进程 cmd 'cat/etc/os-release' 进行检查(

第 1 步：收集 jvm/java 的库 - 启动 Ubuntu 1804 GCP VM 实例并运行以下命令

sudo apt update
#install java on ubuntu
sudo apt install default-jre
#go to the installation directory
cd /usr/lib
#copy the jvm dir to <user_home>, for zipping it properly 
cp -r jvm /home/<user>
cd /home/<user>
#install zip
sudo apt install zip
#zip the jvm directory 
zip -r jvm.zip jvm
#upload the zipped jvm.zip to gcs_bucket (however you wish, gsutil/download it from ssh browser and then manually uploading to gcs bucket) 
gsutil cp jvm.zip gs://<bucket-name> 

步骤 2：将源数据库引擎特定的驱动程序 jar 上传到同一gcs_bucket

步骤 3：创建包含以下代码片段的云函数

在此处使用 HTTP 触发器云函数(您可以使用任何(

main.py

#Importing all the required Libaries
import os
import jaydebeapi
import zipfile
from google.cloud import storage 
from google.cloud.exceptions import NotFound

def make_dir(dir_name):
"""Function to make directory"""
try:
if not os.path.exists(dir_name):
os.makedirs(dir_name)
print('Made an directory named: {}'.format(dir_name))
except OSError as e:
print("Error: %s : %s" % (dir_name, e.strerror))

def unzip(path_to_zip_file,directory_to_extract_to):
"""function to unzip the .zip file"""
try:
if os.path.exists(path_to_zip_file):
with zipfile.ZipFile(path_to_zip_file, 'r') as zip_ref:
zip_ref.extractall(directory_to_extract_to)
print('{} is extracted to {} directory'.format(path_to_zip_file,directory_to_extract_to))
else:
logger.debug('The file: {} does not exist'.format(file_name) )
except OSError as e:
print("Error: %s : %s" % (dir_name, e.strerror))

def download_gcs_file(bucket_name, gcs_path, local_path,projectid):
"""Func to download the file from gcs_bucket"""
storage_client = storage.Client(project=projectid)
try:
bucket = storage_client.get_bucket(bucket_name)
try:
file_blob = bucket.get_blob(gcs_path)
file_blob.download_to_filename('{}'.format(local_path))
except NotFound:
print('File Not Found for downloading File:{}'.format(bucket_name,gcs_path))
raise
except NotFound:
print('Bucket: {} Not Found for downloading File:{}'.format(bucket_name,gcs_path))
raise

def hello_world(request):
"""main function"""
#since the working directory of cloud function runtime is /usercode, which is only read_only_filesystem, so we will create /tmp directory for downloading the jvm.zip and driver jar
tmp_dir='/tmp/db_java'
make_dir(tmp_dir)

jvm_local_path='{}/jvm.zip'.format(tmp_dir) #jvm.zip will be downloaded here from gcs bucket
jdbc_driver_loc='{}/<your-driver-jar>.jar'.format(tmp_dir) #jar will be downloaded here from gcs
jdbc_class_name='<jdbc-driver-main-class>' #your jdbc_driver_main class
download_gcs_file('<bucket-name>', 'jvm.zip', jvm_local_path,'<gcp-project-id>')
download_gcs_file('<bucket-name>', '<your-driver-jar>.jar', jdbc_driver_loc,'<gcp-project-id>')
unzip(jvm_local_path,tmp_dir)
jvm_path='{}/jvm'.format(tmp_dir) 
os.environ['JAVA_HOME']=jvm_path #setting the JAVA_HOME path
user = '<db-username>'
password = '<db-password>'
jdbc_connection_string='<your-jdbc-connection-string>'
conn = jaydebeapi.connect(jdbc_class_name,
jdbc_connection_string,
[user, password],
jdbc_driver_loc)
import pandas as pd
query='<your-sql-query>'
df=pd.read_sql(query, conn)
print(df)

要求.txt

jaydebeapi
pandas
google
google-cloud
google-cloud-storage==1.28.0

您可能会遇到超出内存限制的错误;如果错误"没有这样的文件或目录："/usr/lib/jvm"仍然存在，请将云函数的 ENV 变量设置为 UI 中的 JAVA_HOME=/tmp/db_java/jvm，并在一段时间后重试

JaydebeApi 使用jdbc_connection字符串引用 JaydebeApi pypi 或数据库引擎特定文档

已经为其他数据库引擎测试了该方法，但不是oracle(如果oracle需要更多的操作系统包，那么我不确定这种方法是否有效，但值得一试(