Python SQLContext.jsonRDD方法代码示例

# 需要导入模块: from pyspark.sql import SQLContext [as 别名]
# 或者: from pyspark.sql.SQLContext import jsonRDD [as 别名]
class TestSQL(PySparkTestCase):

    def setUp(self):
        PySparkTestCase.setUp(self)
        self.sqlCtx = SQLContext(self.sc)

    def test_basic_functions(self):
        rdd = self.sc.parallelize(['{"foo":"bar"}', '{"foo":"baz"}'])
        srdd = self.sqlCtx.jsonRDD(rdd)
        srdd.count()
        srdd.collect()
        srdd.schemaString()
        srdd.schema()

        # cache and checkpoint
        self.assertFalse(srdd.is_cached)
        srdd.persist(StorageLevel.MEMORY_ONLY_SER)
        srdd.unpersist()
        srdd.cache()
        self.assertTrue(srdd.is_cached)
        self.assertFalse(srdd.isCheckpointed())
        self.assertEqual(None, srdd.getCheckpointFile())

        srdd = srdd.coalesce(2, True)
        srdd = srdd.repartition(3)
        srdd = srdd.distinct()
        srdd.intersection(srdd)
        self.assertEqual(2, srdd.count())

        srdd.registerTempTable("temp")
        srdd = self.sqlCtx.sql("select foo from temp")
        srdd.count()
        srdd.collect()

# 需要导入模块: from pyspark.sql import SQLContext [as 别名]
# 或者: from pyspark.sql.SQLContext import jsonRDD [as 别名]
def writeLumbarReadings(time, rdd):
	try:
		# Convert RDDs of the words DStream to DataFrame and run SQL query
		connectionProperties = MySQLConnection.getDBConnectionProps('/home/erik/mysql_credentials.txt')
		sqlContext = SQLContext(rdd.context)
		if rdd.isEmpty() == False:
			lumbarReadings = sqlContext.jsonRDD(rdd)
			lumbarReadingsIntermediate = lumbarReadings.selectExpr("readingID","readingTime","deviceID","metricTypeID","uomID","actual.y AS actualYaw","actual.p AS actualPitch","actual.r AS actualRoll","setPoints.y AS setPointYaw","setPoints.p AS setPointPitch","setPoints.r AS setPointRoll")
			assembler = VectorAssembler(
						inputCols=["actualPitch"], # Must be in same order as what was used to train the model.  Testing using only pitch since model has limited dataset.
						outputCol="features")
			lumbarReadingsIntermediate = assembler.transform(lumbarReadingsIntermediate)

			
			predictions = loadedModel.predict(lumbarReadingsIntermediate.map(lambda x: x.features))
			predictionsDF = lumbarReadingsIntermediate.map(lambda x: x.readingID).zip(predictions).toDF(["readingID","positionID"])
			combinedDF = lumbarReadingsIntermediate.join(predictionsDF, lumbarReadingsIntermediate.readingID == predictionsDF.readingID).drop(predictionsDF.readingID)
			
			combinedDF = combinedDF.drop("features")
			
			combinedDF.show()


			combinedDF.write.jdbc("jdbc:mysql://localhost/biosensor", "SensorReadings", properties=connectionProperties)
	except:
		pass

# 需要导入模块: from pyspark.sql import SQLContext [as 别名]
# 或者: from pyspark.sql.SQLContext import jsonRDD [as 别名]
def writeLumbarTrainingReadings(time, rddTraining):
	try:
		# Convert RDDs of the words DStream to DataFrame and run SQL query
		connectionProperties = MySQLConnection.getDBConnectionProps('/home/erik/mysql_credentials.txt')
		sqlContext = SQLContext(rddTraining.context)
		if rddTraining.isEmpty() == False:
			lumbarTrainingReading = sqlContext.jsonRDD(rddTraining)
			lumbarTrainingReadingFinal = lumbarTrainingReading.selectExpr("deviceID","metricTypeID","uomID","positionID","actual.y AS actualYaw","actual.p AS actualPitch","actual.r AS actualRoll","setPoints.y AS setPointYaw","setPoints.p AS setPointPitch","setPoints.r AS setPointRoll")
			lumbarTrainingReadingFinal.write.jdbc("jdbc:mysql://localhost/biosensor", "SensorTrainingReadings", properties=connectionProperties)
	except:
		pass

# 需要导入模块: from pyspark.sql import SQLContext [as 别名]
# 或者: from pyspark.sql.SQLContext import jsonRDD [as 别名]
    sys.exit(-1)
#
# get the data diretory
#
data_dir = sys.argv[1]
#
# Specify local mode and two cores
#
sc = SparkContext("local[2]", "pubnub_devices")

devicesRDD = sc.textFile(data_dir)
#
# create a dataframe
#
sqlContext = SQLContext(sc) 
df = sqlContext.jsonRDD(devicesRDD)
#
# show the infered schema
#
df.show()
#
# register a temp table and issue some SQL like queries to our device information
#
df.registerTempTable("deviceTables")
#
# QUERY 1: get all devices with temp > 20 and humidity < 50
#
results = sqlContext.sql("select device_id, device_name, humidity, temp from deviceTables where temp > 20 and humidity < 50")
# results of a SQL query is another RDD
# interate over the results
#