Method, device, equipment, medium and program product for transmitting multi-province account period data

Abstract

The present invention provides a method, device, equipment, medium and program product for multi-province account period data transmission, which belongs to the field of information technology. This method starts the shell script of PySpark and sets the wide table file name as a variable with the same name as the wide table, thereby realizing the transfer of multiple wide tables without writing multiple shell scripts, thereby improving the simplicity of the method. In the shell script that starts PySpark, the table name and account period selection are defined as variables that can be automatically or non-automatically input, thereby achieving the purpose of flexible conversion of variables. The command line is used to calculate the number of provinces with files larger than 0M in the new account period of the HDFS path source table, avoiding the situation where some provinces have partitions but no data. The time function and string interception function of the python code are used to generate the monthly account period, avoiding the external parameter transfer of PySpark, and also facilitating the date results generated by the local python test code. When transferring multiple full-scale province wide tables with different account periods, this method can transfer data to different databases in a short time, flexible manner and refined script.

Description

Technical Field

[0001] The present invention relates to the field of information technology, and in particular to a method, device, equipment, medium and program product for transmitting multi-province account period data. Background Art

[0002] In a data warehouse in a production environment, it is often necessary to receive national data from multiple business wide tables in different billing periods from different databases. This multi-dimensional data transfer requires setting the wide table name, billing period, and province. The actual situation is often complex and changeable. First, the underlying data volume of the wide tables of some provinces is large, and the processing performance of hive is limited, resulting in occasional wide table results. Some provinces have partitions without data. Therefore, when transferring data, it is necessary to manually check whether the data volume of the 31 provinces is complete from hive; secondly, the transfer fields of different wide tables are different, so it is necessary to write multiple python data transfer scripts, and when starting PySpark for execution, it is necessary to write multiple lines of shell scripts to start PySpark separately to run the corresponding wide table python scripts. This method will make full use of variables to solve this problem; finally, the billing period of each transfer is different, and manual modification increases the workload. When transferring data in multiple dimensions, it is easy to cause redundant code and waste too much time. This application aims to effectively solve the above problems. Summary of the Invention

[0003] Purpose of the invention: To propose a method, device, equipment, medium and program product for transmitting multi-provincial account period data, which aims to solve the above-mentioned problems raised by the prior art by transferring multiple full-scale province-wide tables of different account periods in a short time, flexible manner and refined script.

[0004] First, a multi-province account period data transmission method is proposed, and the steps are as follows:

[0005] S1. Write the Spark configuration, source and receiving tables, and the data extraction SQL statement into a Python data transfer script. Use Python to set the billing period as a variable and concatenate the SQL statements.

[0006] S2. Name the Python data transfer script "wide table name.py";

[0007] S3. In the shell script that starts PySpark, define the wide table name and bill period as variables that can be automatically or non-automatically input;

[0008] S4. Use the command line to calculate the number of provinces with files larger than 0 MB in the new account period of the HDFS path source table.

[0009] S5. Determine the number of provinces:

[0010] When the number of provinces reaches the full value, PySpark is started to run the Python script corresponding to the wide table;

[0011] If the full amount is not reached, the program stops for a predetermined period of time and then checks the province amount again. This process is repeated for a predetermined number of times. If the province still does not reach the full amount, the program is terminated.

[0012] In a further embodiment of the first aspect, step S1 further comprises:

[0013] S1-1. Get the current time datetimeNow;

[0014] S1-2. Subtract 30 days from the current time datetimeNow to obtain the account month last_month of the transferred number.

[0015] S1-3. Get the first 7 digits of the billing month to get the billing month_id;

[0016] S1-4. Concatenate the account period month_id after the SQL statement.

[0017] In a further embodiment of the first aspect, step S3 further includes: defining the wide table name and the account period as variables that can be automatically or non-automatically input; after writing the Python script for transferring data, starting PySpark to run the Python script; and indicating the account period and wide table name of the current data province at startup.

[0018] In a further embodiment of the first aspect, when testing a fixed month and wide table transfer number, the wide table name and account period are fixed and variables are automatically entered.

[0019] In a further embodiment of the first aspect, when rotating months or transferring numbers in a wide table, the table name and bill period are set to non-automatic input, and the variables are manually input:

[0020] Set the table name as the first parameter passed to the shell script, and set the payment period as the second parameter passed to the shell script.

[0021] In a further embodiment of the first aspect, when the months do not rotate and the wide table transfer numbers rotate, the account period is written in a fixed manner and automatically input; the wide table transfer numbers are manually input.

[0022] In a further embodiment of the first aspect, the province and account period in the wide table are both partition fields, and the command line is used to automatically calculate the number of provinces with "files larger than 0M" in the new account period of the HDFS path.

[0023] In a second aspect, a multi-province bill period data transmission device is proposed, the device comprising:

[0024] The acquisition module is used to write the Spark configuration, source table, receiving table, and data extraction SQL into the Python data transfer script. Python is used to set the billing period as a variable and splice the SQL statement.

[0025] Naming module, used to name the Python data transfer script as "wide table name.py";

[0026] A definition module is used to define the wide table name and the bill period as variables that can be automatically or non-automatically input in the shell script that starts PySpark;

[0027] The calculation module is used to calculate the number of provinces with files larger than 0MB in the new account period of the HDFS path source table;

[0028] The judgment module is used to determine the number of provinces: when the number of provinces reaches the full amount, PySpark is started to run the Python script corresponding to the wide table; if the full amount is not reached, the program stops for a predetermined period of time and then checks the number of provinces again. This process is repeated for a predetermined number of times. If the number of provinces still does not reach the full amount, the program is terminated.

[0029] In a third aspect, an electronic device is proposed, which includes: a processor and a memory storing computer program instructions; when the processor executes the computer program instructions, it implements the multi-province account period data transmission method as described in the first aspect.

[0030] In a fourth aspect, a computer-readable storage medium is proposed, in which at least one executable instruction is stored. When the executable instruction is run on an electronic device, the electronic device executes the multi-province account period data transmission method as described in the first aspect.

[0031] In the fifth aspect, a computer program product is proposed. When the instructions in the computer program product are executed by the processor of an electronic device, the electronic device executes the multi-province account period data transmission method as described in the first aspect.

[0032] Compared with the existing technology, this application has obvious technical advantages:

[0033] The shell script that starts PySpark sets the wide table file name as a variable with the same name as the wide table. This allows you to transfer multiple wide tables without writing multiple shell scripts, which improves the simplicity of the method.

[0034] In the shell script that starts PySpark, define the table name and bill period selection as variables that can be automatically or non-automatically input, thereby achieving the purpose of flexible variable conversion.

[0035] When checking whether the data volume of multiple provinces is complete, a shell command is used to automatically loop and check the number of provinces with "files larger than 0M" under the corresponding provincial partition in the new account period of the HDFS path, avoiding the situation where some provinces have partitions but no data in the Hive table.

[0036] Using the time function and string interception function in the Python code to generate monthly account periods not only avoids the external parameter passing method of PySpark, but also facilitates quick testing of the date results generated by the code in the local Python environment. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 This is a flowchart of a method for transmitting multi-province account period data in one embodiment of the present invention.

[0038] Figure 2 This is a structural diagram of a multi-province account period data transmission device in one embodiment of the present invention. DETAILED DESCRIPTION

[0039] In the following description, numerous specific details are provided to provide a more thorough understanding of the present invention. However, it will be apparent to those skilled in the art that the present invention may be practiced without one or more of these details. In other instances, certain technical features well known in the art have not been described to avoid confusion with the present invention.

[0040] Example 1:

[0041] The applicant's research found that existing patents based on PySpark primarily utilize PySpark for data analysis, including data preprocessing, data generation, data normalization, data weight assignment, and data classification calculations. Few patents utilize PySpark for heterogeneous big data transmission, while most big data transmission methods utilize Spark programs reading HBase.

[0042] While some PySpark patents use PySpark logic to execute automated test scripts corresponding to multiple data clusters, monitor the data in each cluster through these automated test scripts, and display the monitoring results to a preset receiving platform, this method uses the number of provinces monitored as the basis for determining whether to start PySpark. The detection results of the automated test scripts are only one step, not the final result, and do not need to be displayed.

[0043] To this end, this embodiment provides a multi-province big data transmission method based on PySpark, which strives to transfer data across databases in a short time, flexible manner, and refined script when transferring multiple full-scale province-wide tables with different account periods.

[0044] The technical solution of this invention mainly writes the specific data transfer tables and fields in the Hive library into the Python data transfer script. Then, after looping to check whether the source table and province data in the HDFS path are complete, PySpark is started to run the Python script to transfer the data to the ClickHouse database. The simplified process is represented as hive-python-HDFS-PySpark-clickhouse. The main steps include:

[0045] S1. Write the Spark configuration, source table, receiving table, and data extraction SQL into a Python data transfer script. Use Python to set the billing period as a variable and concatenate the SQL statements.

[0046] S2. The Python data transfer script is directly named wide table name.py.

[0047] S3. In the shell script that starts PySpark, define the wide table name and billing period as variables that can be automatically or non-automatically entered.

[0048] S4. Use the command line to calculate the number of provinces with files larger than 0 MB in the new account period of the HDFS path source table.

[0049] S5. Check the number of provinces. If the number of provinces reaches the full limit, start PySpark and run the Python script for the corresponding wide table. If the number of provinces is not full, the program stops for 30 minutes and checks the number of provinces again. This process repeats four times. If the number of provinces is still not full, the program terminates.

[0050] Example 2:

[0051] Based on Example 1, Example 2 discloses the detailed steps of each step.

[0052] S1. Write the source table, receiving table, and Spark configuration into a Python data transfer script. Use Python to set the billing period as a variable and concatenate SQL statements.

[0053] Real-world big data environments typically use the Hive database, while product development typically relies on databases like ClickHouse and MySQL. Therefore, we often need to regularly transfer data from multiple wide tables from the Hive database to the ClickHouse database. Each transfer requires a different payment period, and the payment period in SQL must also be modified accordingly, making each manual modification time-consuming and labor-intensive. This method writes the specific transfer source tables, fields, and receiving tables, along with the Spark configuration, into a Python transfer script. The payment period is set as a variable and appended to the end of the SQL statement. This eliminates the need for external parameter transfer and manual payment period changes when running scheduled tasks quarterly.

[0054] S101. Get the current time, such as: datetime.date(2022,12,7)

[0055] S102. Subtract 30 days from the current time to get the current month. After subtracting 30 days, the result is: '2022-11-07'

[0056] S103. Get the first 7 digits of the month of the billing period and delete the '-' in the character string to obtain month_id = '202211'.

[0057] S104. Concatenate the account period after the SQL statement, such as: sql_text+str(month_id).

[0058] S2. Name the Python data transfer script widetablename.py.

[0059] To improve script calling convenience and variable setting flexibility, directly name the Python script "wide table name.py" so that you can call the corresponding Python script directly based on different wide table name variables. For example, if multiple wide tables are named MOBILE_CLIENTS, BRD_CLIENTS, and LANDLINE_CLIENTS, then the three data transfer scripts would be named mobile_clients.py, brd_clients.py, and landline_clients.py respectively.

[0060] S3. In the shell script that starts PySpark, define the table name and bill period as variables that can be entered automatically or non-automatically.

[0061] 1. Define both the table name and bill period as variables that can be entered automatically or not, facilitating quick switching when needed. After writing the data transfer Python script, start PySpark to run the Python script. When starting the script, specify which bill period's data province to check and which table's data to run.

[0062] When testing fixed months and wide tables, you can set the table name and billing period to be fixed and automatically enter variables. For example, if the table name is set to BRD_CLIENTS and the billing period is set to one month ago.

[0063] 2. When you frequently rotate months or transfer data across a wide table, you can set both the table name and the payment period to non-automatic input, and manually enter the two variables. For example, set the table name as the first parameter passed in by the shell script, and set the payment period as the second parameter passed in by the shell script.

[0064] 3. When the months do not need to be rotated and the wide table changes frequently, the account period can be written in a fixed manner and entered automatically, while the wide table is entered manually due to its constant changes.

[0065] S4. Calculate the number of provinces with files larger than 0 MB in the new account period of the HDFS path source table.

[0066] HDFS is a distributed file storage system used by Hadoop. It can store and process large amounts of data. The Hive source table data mentioned above is all stored in HDFS. You can then use the hdfs dfs command to display and search for files in the HDFS path.

[0067] Province and account period (month) in the wide table are both partition fields. If you use the command line to automatically check the number of provinces in the corresponding wide table in HDFS (without limiting file size), some provinces, despite having partitions, have corresponding files with a size of 0MB, and therefore no data for those provinces is available in the wide table. Therefore, this method does not directly calculate the number of provinces in the wide table in HDFS, but instead counts the number of provinces whose HDFS paths have files larger than 0MB.

[0068] For example, if you need to transfer the account period data for April 2023, first check the number of provinces with files larger than 0MB in the 202304 partition. If the number is 31, it means that the data for the new account period of April 2023 for all 31 provinces is in place and can be transferred to the production database.

[0069] S5. Determine the number of provinces and decide whether to start PySpark to run the Python script corresponding to the wide table.

[0070] For example, if there are 31 provinces, when the number of provinces returns 31, PySpark will immediately start running the Python script corresponding to the wide table. If the number of provinces does not return 31, the program will terminate half an hour later and check the number of provinces again. This process will repeat four times. If the number of provinces is still not equal to 31, the program will terminate.

[0071] Example 3:

[0072] Example 3 is based on the above-mentioned Example 2 and discloses some details and code examples in the steps.

[0073] 1. Write the source table, receiving table, and Spark configuration into a Python data transfer script, set the payment period as a variable, and concatenate SQL statements.

[0074] In the embodiment, there are seven wide tables: public mobile MOBILE_CLIENTS, public broadband BRD_CLIENTS, public landline LANDLINE_CLIENTS, public partner PARTNER_CLIENTS, 5G mobile MOBILE_5G_CLIENTS, 5G partner PARTNER_5G_CLIENTS, and international roaming GUOMAN_CLIENTS. Therefore, the following seven Python data transfer scripts are written respectively and named strictly according to the wide table names:

[0075] mobile_clients.py

[0076] brd_clients.py

[0077] landline_clients.py

[0078] partner_clients.py

[0079] mobile_5g_clients.py

[0080] partner_5g_clients.py

[0081] guoman_clients.py

[0082] The specific content of the Python data transmission script takes broadband as an example:

[0083] #-*-coding:utf-8-*-

[0084] #Configure Spark

[0085] import os

[0086] import sys

[0087] reload(sys)

[0088] sys.setdefaultencoding("utf-8")

[0089] spark_name=os.environ.get('SPARK_HOME',None)

[0090] sys.path.insert(0,os.path.join(spark_name,'python'))

[0091] sys.path.insert(0,os.path.join(spark_name,'python / lib / py4j-0.10.4-src.zip'))exec(open(os.path.join(spark_name,'python / pyspark / shell.py')).read())

[0092] from pyspark.sql import SparkSession

[0093] import time

[0094] import datetime

[0095] spark=SparkSession.builder.master("spark: / / master:7077").appName("Word Count").config('spark.jars',

[0096] '~ / module / mysql-connector-java-8.0.29.jar').getOrCreate()

[0097] #Set the payment period to a variable

[0098] datetimeNow=datetime.date.today()

[0099] last_month=str(datetimeNow-datetime.timedelta(days=30))#How many days in advance month_id=last_month[0:7].replace('-',”)

[0100] print('month_id:',month_id)#month_id='202206'

[0101] #Extract source table fields

[0102] sql_text="select

[0103] uuid,prvnce_name,latn_id,latn_name,cust_type,own_cust_id,dial_account,reserve_a,reserve_b,reserve_c,is_available,prov_id,month_id from brd_clients where month_id="

[0104] #Concatenate the variable month_id to the SQL statement

[0105] df1=spark.sql(sql_text+str(month_id))

[0106] #Pass table fields to clickhouse

[0107] df1.repartition(15).write.format("jdbc").options(url="jdbc:clickhouse: / / *.*.*.*:* / dwh?useSSL=false&rewriteBatchedSt atements="true",driver="com.github.housepower.jdbc.ClickHouseDriver",dbtable="brd_clients",user="default",password="

[0108] ",batchsize=2000000,isolationLevel='NONE',truncate='true').mode('append').save()

[0109] 2. Write the process of checking the number of provinces and starting PySpark into the shell startup script

[0110] First, check whether the wide table contains all provinces. If all 31 provinces are present, start PySpark and run the corresponding widetable.py program to transfer data. If all 31 provinces are present, stop for 30 minutes and repeat this process four times. Finally, if all 31 provinces are still present, stop the program.

[0111] An example of the shell startup script check_cp_prov.sh is as follows:

[0112] #! / bin / bash

[0113] LANG=zh_CN.UTF-8

[0114] export NLS_LANG=american_america.AL32UTF8

[0115] # Input parameter wide table name table_name and account period month_id

[0116] #table_name=brd_clients #If the table name is frequently rotated, comment out the line month_id=`date-d'1month ago'+"%Y%m"`

[0117] #datanums=31#To comment out the line datanums=`hdfs, the total number of provinces in the example is 31

[0118] #When executing the script, if the number of additional command line parameters is not 0, the first external input parameter is table_name and the second parameter is month_id. In this example, the account period remains unchanged, the table name rotates and is input from the outside

[0119] if[$#-ne 0];then

[0120] table_name=$1

[0121] #month_id=$2

[0122] fi

[0123] echo$table_name,$month_id

[0124] #Judge the data in the hive table and start executing after all the province data are complete

[0125] for((i=0;i<4;i++));do

[0126] #Calculate the number of provinces with files larger than 0M in the new account period of the source table

[0127] datanums=`hdfs dfs-du-hfilesys: / / fs / house / hive / cust / cust.db / $table_name / * / month_id=$month_id|awk-F”'{print$3,$5}'|awk'{if($1>0)print$2}'|awk-F' / ”{print$9}'|grep-v'^$'|uniq|wc-l`

[0128] #Judge the province quantity. If it is equal to 31, start PySpark to run the corresponding wide table Python script

[0129]

[0130]

[0131] 3. Enter variables, run shell scripts, and start PySpark

[0132] Run the shell script and enter the following command:

[0133] sh check_cp_prov.sh brd_clients

[0134] The input variable is brd_clients. Since table_name = $1 in the previous step, the first parameter is set to the wide table name. Now table_name = brd_clients, and the wide table corresponds to the broadband table. The program automatically calculates the provincial data size of the broadband table. If the data size of all 31 provinces is greater than 0MB, PySpark will start running the broadband Python script to begin transferring data to ClickHouse's broadband table.

[0135] Example 4:

[0136] This embodiment proposes a multi-province account period data transmission device 600, comprising an acquisition module 601, a naming module 602, a definition module 603, a calculation module 604, and a judgment module 605. The acquisition module 601 is used to write the Spark configuration, source and receiving tables, and the SQL query into a Python data transmission script. Using Python, the account period is set as a variable and the SQL statement is concatenated. The naming module 602 is used to name the Python data transmission script "wide table name.py." The definition module 603 is used to define both the wide table name and the account period as variables that can be automatically or non-automatically input in the shell script that launches PySpark. The calculation module 604 is used to calculate the number of provinces in the HDFS path source table's new account period where files are larger than 0MB. The judgment module 605 is used to determine the number of provinces. When the number of provinces reaches the full count, PySpark is launched to run the Python script corresponding to the wide table. If the full count is not reached, the program pauses for a predetermined period, then checks the province count again. This process is repeated a predetermined number of times. If the province count is still not reached, the program terminates.

[0137] Example 5:

[0138] This embodiment proposes an electronic device, which includes: a processor and a memory storing computer program instructions; when the processor executes the computer program instructions, it implements the multi-province account period data transmission method as described in Examples 1 to 3.

[0139] Specifically, the processor may include a central processing unit (CPU), or an application-specific integrated circuit (ASIC), or may be configured to implement one or more integrated circuits of the embodiments of the present application.

[0140] The memory may include a large capacity memory for data or instructions. By way of example and not limitation, the memory may include a hard disk drive (HDD), a floppy disk drive, a flash memory, an optical disk, a magneto-optical disk, a magnetic tape, or a universal serial bus (USB) drive, or a combination of two or more of these. Where appropriate, the memory may include removable or non-removable (or fixed) media. Where appropriate, the memory may be inside or outside the integrated gateway disaster recovery device. In a specific embodiment, the memory is a non-volatile solid-state memory.

[0141] The memory may include read-only memory (ROM), random access memory (RAM), magnetic disk storage media devices, optical storage media devices, flash memory devices, electrical, optical or other physical / tangible memory storage devices. Thus, generally, the memory includes one or more tangible (non-transitory) computer-readable storage media (e.g., memory devices) encoded with software including computer-executable instructions, and when the software is executed (e.g., by one or more processors), it is operable to perform the operations described with reference to the method according to an aspect of the present disclosure.

[0142] The processor implements any one of the data audit methods in the above embodiments by reading and executing computer program instructions stored in the memory.

[0143] Example 6:

[0144] This embodiment proposes a computer-readable storage medium, which stores at least one executable instruction. When the executable instruction is executed on an electronic device, the electronic device executes the multi-province account period data transmission method described in Examples 1 to 3.

[0145] The functional blocks shown in the above-described block diagram can be implemented as hardware, software, firmware or a combination thereof. When implemented in hardware, they can be, for example, electronic circuits, application specific integrated circuits (ASICs), appropriate firmware, plug-ins, function cards, etc. When implemented in software, the elements of the present application are programs or code segments that are used to perform the required tasks. The program or code segment can be stored in a machine-readable medium, or transmitted on a transmission medium or a communication link by a data signal carried in a carrier wave. "Machine-readable medium" can include any medium capable of storing or transmitting information. Examples of machine-readable media include electronic circuits, semiconductor memory devices, ROMs, flash memories, erasable ROMs (EROMs), floppy disks, CD-ROMs, optical disks, hard disks, optical fiber media, radio frequency (RF) links, etc. The code segment can be downloaded via computer networks such as the Internet and intranets.

[0146] In summary, the above embodiments disclose a method, apparatus, device, medium, and program product for transmitting data for multiple provincial account periods. When checking whether the data volume for multiple provinces is complete, the number of provinces is not manually queried using SQL in the Hive library, nor is it simply checked whether there are files under the HDFS paths for each province. Instead, a shell command is used to automatically loop and check the number of provinces in the new account period in the HDFS path whose files are larger than 0MB. Only when the corresponding account period file for a province is larger than 0MB is it counted in the province volume, thus avoiding the situation where some provinces have partitions but no data in the Hive table. For example, the Shaanxi Province partition exists in the wide table, but the data volume under Shaanxi Province is 0MB. If the file data volume is not determined and the number of files in Shaanxi Province under HDFS is directly calculated, the file number in Shaanxi Province is 1, which will be incorrectly counted in the province volume. Conversely, if it is determined that there is 1 file under Shaanxi Province, but its data volume = 0, Shaanxi Province will not be counted in the province volume.

[0147] Using PySpark to run data transfer Python scripts saves time compared to developing traditional Scala programs. While the typical data transfer method involves running Scala scripts directly with Spark, Scala scripts must be written locally, packaged as a JAR file, and then uploaded to the machine. During development, fields often need to be changed and data transfer scripts need to be modified. Writing and uploading data locally each time increases time and complexity. Python scripts can be edited directly on a Linux machine, eliminating the upload step and making them easier for Python engineers unfamiliar with Scala or Java to develop.

[0148] The shell script that starts PySpark sets the wide table file name as a variable with the same name as the wide table. This allows you to transfer multiple wide tables without writing multiple shell scripts, which improves the simplicity of the method.

[0149] In the shell script that starts PySpark, define the table name and billing period selection as variables that can be automatically or non-automatically entered, allowing for flexible variable conversion. If you frequently rotate wide tables and billing periods, set them to non-automatic input and manually enter them externally. If you consistently test the same wide table and billing period, comment out the code that sets the external variables and modify them to automatically enter the variables.

[0150] When setting the payment period for SQL statements in a Python script, the payment period generated by the time function and string truncation function is concatenated after the SQL statement. This avoids external parameter passing and manual payment period modification, making it more compatible with the PySpark environment and facilitating local testing of date results. Since the Python data transfer script in this method is not executed externally in a single Python environment (i.e., you cannot execute `python xxx.py --month_id 202112`), but rather in a PySpark environment, PySpark cannot easily parse command-line arguments using the argparse module. Setting the payment period variable within the script can also achieve automatic payment period generation. Locally, a Hive environment is generally not available, requiring a virtual machine connection to write HiveSQL statements. As the test time changes, the parameters in the time code also change. Even if you use HiveSQL to write a time variable to test a few lines of time code, you still need to connect to a virtual machine. Therefore, using the time function and string truncation function in Python code to generate the month payment period not only avoids the need for external parameter passing in PySpark, but also facilitates quick testing of the generated date results in a local Python environment.

[0151] While the present invention has been shown and described with reference to certain preferred embodiments, it is not to be construed as limiting the invention itself, and various changes in form and details may be made therein without departing from the spirit and scope of the invention as defined in the appended claims.

Claims

1. A method for transmitting multi-province account period data, characterized in that: The steps include: S1. Write the Spark configuration, source and receiving tables, and the data extraction SQL statement into a Python data transfer script. Use Python to set the billing period as a variable and concatenate the SQL statements. S2. Name the Python data transfer script "wide table name.py"; S3. In the shell script that starts PySpark, define the wide table name and billing period as variables that can be automatically or non-automatically input; define the wide table name and billing period as variables that can be automatically or non-automatically input; after writing the data transfer Python script, start PySpark to run the Python script; at startup, indicate the billing period and wide table name of the current data province; When rotating months or transferring data from a wide table, set both the table name and the billing period to non-automatic input and manually enter the variables: Set the table name as the first parameter passed in by the shell script, and set the payment period as the second parameter passed in by the shell script; When the month does not rotate and the wide table transfer number rotates, the account period is written in a fixed way and automatically input; the wide table transfer number is manually entered; In the wide table, province and account period are both partition fields. Use the command line to automatically calculate the number of provinces with files larger than 0MB in the new account period in the HDFS path. If the number of provinces meets the preset number, PySpark is started to run the Python script for the corresponding wide table and transfer the full data of the new account period to the production database. If it does not meet the preset number, another check is performed; S4. Use the command line to calculate the number of provinces with files larger than 0 MB in the new account period of the HDFS path source table. S5. Determine the number of provinces: When the number of provinces reaches the full value, PySpark is started to run the Python script corresponding to the wide table; If the full amount is not reached, the program stops for a predetermined period of time and then checks the province amount again. This process is repeated for a predetermined number of times. If the province still does not reach the full amount, the program is terminated.

2. The multi-province account period data transmission method according to claim 1 is characterized in that: Step S1 further comprises: S1-1, get the current time; S1-2. Subtract 30 days from the current time to obtain the account month of the transferred number; S1-3, take the first 7 digits of the month of the payment period to get the payment period; S1-4. Add the account period after the SQL statement.

3. The multi-province account period data transmission method according to claim 1, characterized in that: When testing fixed months and wide table transfer numbers, the wide table name and account period are fixed and variables are automatically entered.

4. A multi-province bill period data transmission device, used to execute the multi-province bill period data transmission method according to any one of claims 1 to 3, characterized in that: include: The acquisition module is used to write the Spark configuration, source table, receiving table, and data extraction SQL into the Python data transfer script. Python is used to set the billing period as a variable and splice the SQL statement. Naming module, used to name the Python data transfer script as "wide table name.py"; A definition module is used to define the wide table name and the bill period as variables that can be automatically or non-automatically input in the shell script that starts PySpark; The calculation module is used to calculate the number of provinces with files larger than 0MB in the new account period of the HDFS path source table; The judgment module is used to determine the number of provinces: when the number of provinces reaches the full amount, PySpark is started to run the Python script corresponding to the wide table; if the full amount is not reached, the program stops for a predetermined period of time and then checks the number of provinces again. This process is repeated for a predetermined number of times. If the number of provinces still does not reach the full amount, the termination program is executed.

5. An electronic device, characterized in that: The device includes: a processor and a memory storing computer program instructions; When the processor executes the computer program instructions, it implements the multi-province account period data transmission method as described in any one of claims 1 to 3.

6. A computer-readable storage medium, characterized in that The storage medium stores at least one executable instruction, and when the executable instruction is executed on the electronic device, the electronic device executes the multi-province account period data transmission method as described in any one of claims 1 to 3.

7. A computer program product, characterized in that When the instructions in the computer program product are executed by a processor of an electronic device, the electronic device executes the multi-province account period data transmission method as described in any one of claims 1 to 3.

Images