Read txt files and get MySQL data

[mapplechannel]

1.Hello, when I read the txt file, I found that the read data was not in json format and could not be operated on. My yaml is：
input:
label: ""
sftp:
address: "127.0.0.1:22"
credentials:
username: "admin"
password: "123456"
private_key_file: ""
private_key_pass: ""
paths: [/home/sftp_root/shared/123.txt]
codec: all-bytes
watcher:
enabled: false
minimum_age: 1s
poll_interval: 1s
cache: ""

pipeline:
processors: []

output:
label: ""
stdout:
codec: lines

Please tell me how I should configure it so that the txt content can be turned into a json format data，thanks

2.Another question is that when I read the int value of MySQL, I found that the obtained data became string type. How can I keep the data type unchanged, which is int?

[Jeffail]

Converting to a discussion as per: #2026

[mihaitodor]

Please tell me how I should configure it so that the txt content can be turned into a json format data，thanks

Does the text in /home/sftp_root/shared/123.txt contain a single valid JSON object? This works for me:

{
  "foo": "bar"
}

input:
  file:
    paths:
      - ./foo.txt
    codec: all-bytes

  processors:
    - log:
        message: ${! json("foo") }

output:
  stdout: {}

Output:

INFO Running main config from specified file       @service=benthos path=test.yaml
INFO Listening for HTTP requests at: http://0.0.0.0:4195  @service=benthos
INFO Consuming from file './foo.txt'               @service=benthos label="" path=root.input
INFO Launching a benthos instance, use CTRL+C to close  @service=benthos
INFO bar                                           @service=benthos label="" path=root.input.processors.0
{
  "foo": "bar"
}
INFO Pipeline has terminated. Shutting down the service  @service=benthos

Note the line with "INFO bar ..." which was emitted by the log processor.

Another question is that when I read the int value of MySQL, I found that the obtained data became string type. How can I keep the data type unchanged, which is int?

Looks like that's a limitation of the underlying MySQL client library (YUCK!!!): go-sql-driver/mysql#407 (comment) and go-sql-driver/mysql#861

You can hack around it by using the sql_raw input (or processor) and forcing a prepared statement like so (feel free to use any other SQL query which has at least one placeholder (?) in it):

input:
  sql_raw:
    driver: mysql
    dsn: testuser:testpass@tcp(localhost:3306)/testdb?multiStatements=true
    query: "SELECT FOO, BAR FROM TEST WHERE 1 = ?"
    args_mapping: |
      root = [
        1
      ]
    init_statement: CREATE TABLE IF NOT EXISTS TEST (FOO INT, BAR BOOLEAN); INSERT INTO TEST (FOO, BAR) VALUES (666, 0), (42, 1);

  processors:
    - mutation: |
        root.BAR = this.BAR == 1

output:
  stdout: {}

Since you also asked about booleans in #2108, as you can see from this conversation, you'll need to do the conversion manually on the Benthos side, which is why I added that mutation processor.

[mapplechannel]

@mihaitodor, hey. While working with benthos, I encountered the following problem：read mysql data in processors, traverse the obtained data and generate a new field. The value of the field comes from the addition, subtraction, multiplication and division of the data. When the first bit of the data is of float type, an error will be reported and the subsequent process will be stopped. If the float type is not in the first place, the result can be calculated normally. At the same time, the float type cannot participate in the .max and .min functions. Currently, only the mysql database has such a problem,My MySQL table design is as shown in the figure.Could you tell me how to solve this problem? Thanks.

[mihaitodor]

@mapplechannel I think this thread is getting sidetracked into other issues, so I encourage you to consider wrapping this one here and either starting a new ones for each new topic or join one of the community chat platforms if you'd rather have more interactive conversations.

I just tried calling both min() and max() on an array with mixed floats and integers (i.e. root = [4.1, 3].min() and root = [3, 4.1].max()) and I didn't see any issues with Benthos 4.22.0, so I'm guessing you bumped into some other problem, but I can't really tell based on the description you provided.

[mihaitodor]

@mapplechannel I dug around and raised a PR to address the unrecognised float32 type in #2187. As a workaround, please call .number() on your Column2 and ff fields in bloblang to cast them to a number explicitly.

[mihaitodor]

This should be fixed in v4.23.0.

[mapplechannel]

Wow,thanks a lot!