Integration with MQTT

Hello Yongwei,

Can you also provide us the code you have used in the DatabaseManager class in the copy_all_files_to_vertica.py?

and you able to connect to your vertica 9 instance successfully from other tools?

Thanks.

Hi Yongwei,

We have integrated MQTT with Vertica 11 and Today I have Tested MQTT with Vertica 12.0.1 and it's working fine.

Can you try to upgrade to Vertica 11 or latest version(recommended) of Vertica?

I Hope your Vertica9 is working fine(You can check via VSQL) and you have entered Correct Vertica connection details in database manager class?

Thanks,
Rajasekhar Vuppala

Hi, sure I will test it out tomorrow. this is the code that I'm using.
import vertica_python
import os

class DatabaseManager():
def init(self):
conn_info = {'host': 'host.com', 'port': 5450, 'user': 'dbadmin', 'password': 'password', 'database': 'PREDICTIVEMAINT'}
try:
self.conn = vertica_python.connect(**conn_info)
except Exception as e:
print(e)
self.conn.commit()
self.cur = self.conn.cursor()

def add_del_update_db_record(self, sql_query, args=()):
    self.cur.execute(sql_query, args)
    self.conn.commit()
    return
def copy_JSON_records_to_vertica(self, sql_query, args=()):
    self.cur.copy(sql_query, args)
    self.conn.commit()
    return

def __del__(self):
    self.cur.close()
    self.conn.close()

The below function will be continuosly looking for JSON files with "completed" name in them

def get_all_the_files_list_copy_to_vertica():
while (True):
for i in os.listdir():
if "completed" in i:
print(i)
dbObj = DatabaseManager()
with open(i, "rb") as fs:
dbObj.copy_JSON_records_to_vertica("COPY mqtt.Sensor_Data( DeviceID, pH, EC, Latitude, Longtitude, DateTime) FROM STDIN parser fjsonparser()", fs)
del dbObj
print ("Inserted Sensor Data into Database.")
print ("")
fs.close()
os.remove(i)

get_all_the_files_list_copy_to_vertica()

Hi @yongwei ,

Code looks good.

Can you update the Vertica to Vertica 11 or latest version of Vertica.

Please let us know how it goes.

Hi,
thanks for the support, the code is working now in vertica 9 and previously it doesn't work because i put the wrong credentials