【腾讯云云上实验室】用向量数据库在金融信数据库分析中的实战运用

pip install tcvectordb

import tcvectordb
from tcvectordb.model.enum import FieldType, IndexType, MetricType, ReadConsistency

# 创建数据库连接对象
client = tcvectordb.VectorDBClient(url='http://lb-*******.ap-guangzhou.tencentclb.com:50000', username='root', key='G283v2GaQRJG3vk******', read_consistency=ReadConsistency.EVENTUAL_CONSISTENCY, timeout=30)

# 创建数据库
db = client.create_database(database_name='t_vectordb_demo_01')

print(db.database_name)

# 第一步，设计索引
index = Index(
            FilterIndex(name='id', field_type=FieldType.String, index_type=IndexType.PRIMARY_KEY),
            VectorIndex(name='vector', dimension=768, index_type=IndexType.HNSW,
              metric_type=MetricType.COSINE, params=HNSWParams(m=16, efconstruction=200)),

)

ebd = Embedding(vector_field='vector', field='text', model=EmbeddingModel.BGE_BASE_ZH)

# 第二步：创建 Collection
coll = db.create_collection(
            name='loan_data_analysis',
            shard=1,
            replicas=0,
            description='this is a collection of test embedding',
            embedding=ebd,
            index=index
        )
print(vars(coll))

import tcvectordb
from tcvectordb.model.enum import FieldType, IndexType, MetricType, ReadConsistency
from tcvectordb.model.enum import FieldType, IndexType, MetricType, EmbeddingModel
from tcvectordb.model.index import Index, VectorIndex, FilterIndex, HNSWParams
from tcvectordb.model.collection import Embedding, UpdateQuery
from tcvectordb.model.enum import FieldType, IndexType, MetricType, ReadConsistency
from tcvectordb.model.document import Document, Filter, SearchParams

# # 创建数据库连接对象
client = tcvectordb.VectorDBClient(url='http://lb-******.clb.ap-guangzhou.tencentclb.com:50000', username='root', key='G283v2******', read_consistency=ReadConsistency.EVENTUAL_CONSISTENCY, timeout=30)


# 指定写入原始文本的数据库与集合
db = client.database('t_vectordb_demo_01')
coll = db.collection('loan_data_analysis')


# 写入数据，可能存在一定延迟
# 1. 支持动态 Schema，除了 id、text 字段必须写入，可以写入其他任意字段，text 字段为创建集合时，设置的文本字段名
# 2. upsert 会执行覆盖写，若文档id已存在，则新数据会直接覆盖原有数据(删除原有数据，再插入新数据)
# 3. 参数 build_index 为 True，指写入数据同时重新创建索引。
res = coll.upsert(
    documents=[
        Document(
            id='1077501',
            text="1077501:10+ years",
            author='RENT',
            bookName='5000',
            page=36,
            funded_amnt=5000,
            funded_amnt_inv=4975,
            int_rate=10.65,
            installment=162.87,
            grade='B',
            sub_grade='B2',
            emp_title='',
            emp_length='10+ years',
            home_ownership='RENT'
        ),
        Document(
            id='1077430',
            text="1314167:< 1 year",
            author='RENT',
            bookName='2500',
            page=60,
            funded_amnt=2500,
            funded_amnt_inv=2500,
            int_rate=15.27,
            installment=59.83,
            grade='C',
            sub_grade='C4',
            emp_title='Ryder',
            emp_length='< 1 year',
            home_ownership='RENT'
        )
    ],
    build_index=True
)

注意：
1. 支持动态 Schema，除了 id、text 字段必须写入，可以写入其他任意字段，text 字段为创建集合时，设置的文本字段名
2. upsert 会执行覆盖写，若文档id已存在，则新数据会直接覆盖原有数据(删除原有数据，再插入新数据)
3. 参数 build_index 为 True，指写入数据同时重新创建索引。

# Set filter
filter_param=Filter(Filter.In("text",["year", "years"]))


# query
doc_list = coll.query(document_ids=['1077501','1077430'], retrieve_vector=True, filter=filter_param, limit=2, offset=0, output_fields=['text','author'])


for doc in doc_list:
          print(doc)

# 将术语列转换为数字数据类型

def term_numeric(df, column):
    df[column] = pd.to_numeric(df[column].str.replace(' months', ''))
    
term_numeric(data, 'term')

#converting emp-length to numeric datatype
def emp_length_convert(df, column):
    df[column] = df[column].str.replace('\+ years', '')
    df[column] = df[column].str.replace('< 1 year', str(0))
    df[column] = df[column].str.replace(' years', '')
    df[column] = df[column].str.replace(' year', '')
    df[column] = pd.to_numeric(df[column])
    df[column].fillna(value = 0, inplace = True)
   

# 预处理日期列

def date_columns(df, column):
    # store current month
    today_date = pd.to_datetime('2020-08-01')
    # convert to datetime format
    df[column] = pd.to_datetime(df[column], format = "%b-%y")
    # calculate the difference in months and add to a new column
    df['mths_since_' + column] = round(pd.to_numeric((today_date - df[column]) / np.timedelta64(1, 'M')))
    # make any resulting -ve values to be equal to the max date
    df['mths_since_' + column] = df['mths_since_' + column].apply(lambda x: df['mths_since_' + column].max() if x < 0 else x)
    # drop the original date column
    df.drop(columns = [column], inplace = True)

# 基于loan_status列创建一个新列，这将是我们的目标变量
data['good_bad'] = np.where(data.loc[:, 'loan_status'].isin(['Charged Off', 'Default', 'Late (31-120 days)',
                                                                       'Does not meet the credit policy. Status:Charged Off']), 0, 1)
# Drop the original 'loan_status' column
data.drop(columns = ['loan_status'], inplace = True)

Information value of term is 0.035478
Information value of int_rate is 0.347724
Information value of grade is 0.281145
Information value of emp_length is 0.007174
Information value of home_ownership is 0.017952
Information value of annual_inc is 0.037998
Information value of verification_status is 0.033377
Information value of pymnt_plan is 0.000309
Information value of purpose is 0.028333
Information value of addr_state is 0.010291
Information value of dti is 0.041026
Information value of delinq_2yrs is 0.001039
Information value of inq_last_6mths is 0.040454
Information value of mths_since_last_delinq is 0.002487
Information value of open_acc is 0.004499
Information value of pub_rec is 0.000504
Information value of revol_util is 0.008858
Information value of initial_list_status is 0.011513
Information value of out_prncp is 0.703375
Information value of total_pymnt is 0.515794
Information value of total_rec_int is 0.011108
Information value of last_pymnt_amnt is 1.491828